Re: new columns created from " "

Giganews Newsgroups
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)
>
> 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>

Replies

None

In response to

new columns created from " " posted by Greg Wilker on Tue, 25 May 2004