|Subject:||Re: new columns created from " "|
|Posted by:||Papparotti (Papparot…@o2.ie)|
|Date:||Tue, 25 May 2004|
Greg Wilker wrote:
> I have a bunch of data in a column and it is "separated" by " ".
> The data was imported from an online database.
> Is there a way to create columns within the current workbook from where the
> appears? (much like importing a text file)
" " is a "fixed whitespace" character in HTML.
Personally I would write a class to read in the data.
Note: You'll have to delete all non-data lines from the file (you can
leave the header in as long as they are also seperated by " ".
Below is some source code, if you need any help, just drop me a line.
Additionally you have to write Property Get statements to extract the
Data from the array. something along the line like :
add a class module and name it clsImportFile
========== CLASS ===============================
Property Get Col1() as String
Col1 = Trim(FieldsInLine(0))
Dim clsImportFile As Integer
Dim Buffer As String
Dim FieldsInLine(7) As String ' change the number to the amount of
columns in the file
Sub OpenIt(Path As String)
clsImportFile = FreeFile
If Path = "" Or UCase(Path) = "FALSE" Then
MsgBox "Filename error"
Open Path For Input As clsImportFile
Function moveToNextLine() As Boolean
Dim x As Long
Dim cPos As Long
Dim nPos As Long
If Not EOF(clsImportFile) Then
Line Input #clsImportFile, Buffer
cPos = 1
For x = 0 To UBound(FieldsInLine)
nPos = InStr(cPos, Buffer, "&nsbp;")
On Error Resume Next
FieldsInLine(x) = Mid(Buffer, cPos, nPos - cPos)
cPos = nPos + 1
moveToNextLine = True
moveToNextLine = False
====== END CLASS ===============================
========== MODULE ==============================
add a module and name it like you want.
Dim i as long
dim myFile as new clsImportFile
myFile.openIt(application.getopenfilename("All Files (*.*),*.*"))
======= END MODULE ==============================
new columns created from " " posted by Greg Wilker on Tue, 25 May 2004