Greg said:
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)
TIA,
Greg
Hi Greg
" " 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.
Papparotti
<SourceCode>
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))
End Property
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"
Else
Open Path For Input As clsImportFile
End If
End Sub
Sub CloseIt()
Close clsImportFile
End Sub
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
Next x
moveToNextLine = True
Else
moveToNextLine = False
End If
End Function
====== END CLASS ===============================
========== MODULE ==============================
add a module and name it like you want.
sub importWebFile()
Dim i as long
dim myFile as new clsImportFile
workbooks.add
myFile.openIt(application.getopenfilename("All Files (*.*),*.*"))
i=0
while myFile.movetonextline
i=i+1
cells(i,1)=myFile.Col1
wend
myFile.closeit
end sub
======= END MODULE ==============================
</SourceCode>
