Split(linefromfile, " ") when it is a txt file with spaces deliminating

[MetLife]

Hi,

I am trying to read in from a txt file. The columns are spaced using various lengths of spaces " ". So I can't just use a single space in the code. Does anyone know a way around this?

'Step #1 - find the table #2
For i = 0 To 1500
If EOF(1) Then GoTo L2
Line Input #1, linefromfile
lineitems = Split(linefromfile, " ")
If i < 2 Then
'SKIP HEADERS
Else
'If lineitems(0) = iiage Then
For j = 1 To 15
out(i, j) = lineitems(j - 1)
Next j
'End If
End If
Next i

 

[Akihito Yamashiro]

change

Line Input #1, linefromfile
lineitems = Split(linefromfile, " ")

to

Line Input #1, linefromfile
Do
    linefromfile_prev = linefromfile
    linefromfile = Replace(linefromfile, Space(2), Space(1))
    If linefromfile_prev = linefromfile Then
        Exit Do
    End If
Loop
lineitems = Split(linefromfile, " ")