Read/Parse text file line by line in VBA Read/Parse text file line by line in VBA vba vba

Read/Parse text file line by line in VBA

for the most basic read of a text file, use open


Dim FileNum As IntegerDim DataLine As StringFileNum = FreeFile()Open "Filename" For Input As #FileNumWhile Not EOF(FileNum)    Line Input #FileNum, DataLine ' read in data 1 line at a time    ' decide what to do with dataline,     ' depending on what processing you need to do for each caseWendClose #FileNum

I find the FileSystemObject with a TxtStream the easiest way to read files

Dim fso As FileSystemObject: Set fso = New FileSystemObjectSet txtStream = fso.OpenTextFile(filePath, ForReading, False)

Then with this txtStream object you have all sorts of tools which intellisense picks up (unlike using the FreeFile() method) so there is less guesswork. Plus you don' have to assign a FreeFile and hope it is actually still free since when you assigned it.

You can read a file like:

Do While Not txtStream.AtEndOfStream    txtStream.ReadLineLooptxtStream.Close

NOTE: This requires a reference to Microsoft Scripting Runtime.

For completeness; working with the data loaded into memory;

dim hf As integer: hf = freefiledim lines() as string, i as longopen "c:\bla\bla.bla" for input as #hf    lines = Split(input$(LOF(hf), #hf), vbnewline)close #hffor i = 0 to ubound(lines)    debug.? "Line"; i; "="; lines(i)next