Splitting a string with variable number of spaces VBA

Question

I have a file with a bunch of number in columns. These numbers are separated by variable number of spaces. I want to skip the first line and get all the other lines and separte each number on the line. Finally, I want to write each number on Excel. I've been able to get the lines and write them on Excel but I can't separate each number (I'm getting the whole line as one string).

Does any body know how to split a string that has a variable number of spaces?

Here is my code.

Sub Test()

r = 0

With New Scripting.FileSystemObject
    With .OpenTextFile("C:\Users\User\Desktop\File.tab", ForReading)
        If Not .AtEndOfStream Then .SkipLine
        Do Until .AtEndOfStream
            ActiveCell.Offset(r, 0) = Split(.ReadLine, vbCrLf)
            r = r + 1
        Loop
    End With
End With

End Sub

Answer 1

If you use the Excel worksheet function trim in place of the VBA function then excel will remove multiple spaces within a cell (not just from the left and right ends). Something like the below should solve the problem. I'm afraid I've not tested it as I haven't a copy of Excel handy.

Sub Test()

Dim splitValues As Variant
Dim i As Long

r = 0

With New Scripting.FileSystemObject
    With .OpenTextFile("C:\Users\User\Desktop\File.tab", ForReading)
        If Not .AtEndOfStream Then .SkipLine
        Do Until .AtEndOfStream
            ActiveCell.Offset(r, 0) = Split(.ReadLine, vbCrLf)
        Application.Trim(ActiveCell.Offset(r, 0))
        splitValues = Split(ActiveCell.Offset(r, 0), " ")

        For i = 0 To UBound(x)
            ActiveCell.Offset(r, i+1) = splitValues(i)
        Next
        Loop
    r = r + 1
    End With
End With