Preserving "columns" when writing from a text file to excel using VBA

Question

I have a text file that is formatted in the following way:

And I am using the below code in VBA to write the text file into excel:

Sub Test()

 Dim Fn As String, WS As Worksheet, st As String

 Fn = "Path.txt" ' the file path and name
 Set WS = Sheets("Sheet1")

 'Read text file to st string
 With CreateObject("Scripting.FileSystemObject")
    If Not .FileExists(Fn) Then
        MsgBox Fn & "  : is missing."
        Exit Sub
    Else
        If FileLen(Fn) = 0 Then
            MsgBox Fn & "  : is empty"
            Exit Sub
        Else
            With .OpenTextFile(Fn, 1)
             st = .ReadAll
             .Close
            End With
        End If
    End If
 End With

 'Replace every two or more space in st string with vbTab
 With CreateObject("VBScript.RegExp")
  .Pattern = "[ ]{2,}"
  .Global = True
  .Execute st
  st = .Replace(st, vbTab)
 End With

 'Put st string in Clipboard
 With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .SetText st
    .PutInClipboard
 End With

 'Paste Clipboard to range
 WS.Range("A1").PasteSpecial

End Sub

My goal is to preserve the columns from the text file in Excel.

However, my code can't tell that a blank space under Plan Type and a blank space under Benefit Plan are actually two different columns of data. It treats the blank space under the two columns as one long blank space, and the formatting isn't preserved.

Visually we know there are columns, but my code cannot see this.

Is there a way to program this so it recognizes that there are two spaces in the text file instead of one big space?

What I want to avoid is having to manually deliminate this with a character. Is that possible?

Answer 1

Assuming that each column is 10 characters long, I would use this width instead of a space delimeter

Sub FeedTextFileToActiveSheet(ByVal TextFile As String)
  Dim i As Integer, Line As String
  Open TextFile For Input As #1
  While Not EOF(#1)
    i = i + 1
    Input #1, Line
    Range("A" & i) = Trim(Mid(Line, 1, 10))  'Business ID
    Range("B" & i) = Trim(Mid(Line, 11, 10)) 'Employee ID
    ' ... and so on
  Wend
  Close #1
End Sub

To use it, just call FeedTextFileToActiveSheet("Path.txt")