Import a txt file into excel and format with text to column

Question

I am attempting to import a .txt file into Excel via VBA code and then format the content with a text to column command.

The txt file holds content in the following:

DATE | 1 | 2 | 3 | 4 | Something ||||| Not Sure |||||
DATE | 5 | 6 | 7 | 8 | New ||||| Whatever |||||

Currently, using code I've found and slammed together, I've managed to get this far

Sub Sample()
    Dim MyData As String, strData() As String, myFile As String

    myFile = Application.GetOpenFilename()

    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, "|")


End Sub

This merely gets all of the data from the txt file and separates each item into an array.

I'd like to put the items from the array into columns of excel starting from Range("A5") AND account for each new row.

Help?

(Edit: I thought of moving down a row anytime I get to a empty array selection, but there are many blanks within each row and this wouldn't work. Also, the lengths of the rows are inconsistent depending on content.)

Answer 1

You need to Split the data two ways: into lines using the NewLine character, then into cells using |

Note that the line break chacter in your text file may not be vbNewLine. If this code doesn't split into lines, thats the first place to look.

To complete your code as poseted, try

Sub Sample()
    Dim MyData As String
    Dim lineData() As String, strData() As String, myFile As String
    Dim i As Long, rng As Range

    ' lets make it a little bit easier for the user
    myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")

    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    ' Split into wholes line
    lineData() = Split(MyData, vbNewLine)
    Set rng = Range("A5")
    ' For each line
    For i = 0 To UBound(lineData)
        ' Split the line
        strData = Split(lineData(i), "|")
        ' Write to the sheet
        rng.Offset(i, 0).Resize(1, UBound(strData) + 1) = strData
    Next
End Sub

As an alternative, treat the .txt file as, well, Text

Sub Sample()
    Dim fn As Integer
    Dim MyData As String
    Dim lineData As String, strData() As String, myFile As String
    Dim i As Long, rng As Range

    myFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")

    Set rng = Range("A5")

    ' Lets not rely on Magic Numbers
    fn = FreeFile
    Open myFile For Input As #fn
    i = 1
    Do While Not EOF(fn)
        Line Input #fn, lineData
        strData = Split(lineData, "|")
        rng.Cells(i, 1).Resize(1, UBound(strData) + 1) = strData
        i = i + 1
    Loop
    Close #fn
End Sub