Read/Parse text file line by line in VBA

user_817559 0
Viewed 421756

I'm trying to parse a text document using VBA and return the path given in the text file. For example, the text file would look like:

*Blah blah instructions
*Blah blah instructions on line 2
G:\\Folder\...\data.xls
D:\\AnotherFolder\...\moredata.xls

I want the VBA to load 1 line at a time, and if it starts with a * then move to the next line (similar to that line being commented). For the lines with a file path, I want to write that path to cell, say A2 for the first path, B2 for the next, etc.

The main things I was hoping to have answered were:

  1. What is the best/simple way to read through a text file using VBA?
  2. How can I do that line by line?
vba excel
5 Answers

The below is my code from reading text file to excel file.

Sub openteatfile()
Dim i As Long, j As Long
Dim filepath As String
filepath = "C:\Users\TarunReddyNuthula\Desktop\sample.ctxt"
ThisWorkbook.Worksheets("Sheet4").Range("Al:L20").ClearContents
Open filepath For Input As #1
i = l
Do Until EOF(1)
Line Input #1, linefromfile
lineitems = Split(linefromfile, "|")
        For j = LBound(lineitems) To UBound(lineitems)
            ThisWorkbook.Worksheets("Sheet4").Cells(i, j + 1).value = lineitems(j)
        Next j
    i = i + 1 
Loop
Close #1
End Sub
user_41538950
Related