How to Read a Text File Line by Line in VBA

1. Use Open to Read a Text File Line by Line in VBA
2. Use FileSystemObject With TextStream to Read a Text File Line by Line in VBA
3. Conclusion

Do you want to transfer all the emails stored in a file to Microsoft Excel for further processing? Or do you wish to keep the information of some shortlisted candidates in a file?

You can efficiently perform all these tasks with the help of simple automated programs.

Reading a text file in a program is reasonably necessary. Almost every other code requires input from a file or output to a file; therefore, it is essential to understand how to read an entire file line by line, character by character, etc.

This article emphasizes reading a text file line by line. You can keep exploring the different methods of reading a text file and implementing them according to the requirements.

Use Open to Read a Text File Line by Line in VBA

There are only a few different VBA options to open or close a file. Only a few commands are available to open and close a workbook, file, or folder.

Following is a code snippet that opens a file and reads through the entire file, line by line.

Sub example()

    Dim FileNumber As Integer
    Dim Data As String

    FileNumber = FreeFile()
    Open "Filename" For Input As #FileNumber

    While Not EOF(1)
        Line Input #FileNumber, Data
        //Read a data line from the file
        //Perform any required actions on the file
    Wend

    Close #FileNumber
End Sub

The program mentioned above is a simple code snippet that allows you to open a file, read data from it, and close it. It declares the FileNumber and Data variables throughout the code.

To get the FileNumber, the FreeFile() function is used. The FreeFile() function returns the next available file number to open the file.

The FileNumber returned by the FreeFile() function is used throughout the program to open the file, read from it, and close it.

Note: It is essential to close every file that is opened throughout the program.

After the FileNumber is returned by the FreeFile() function, the Open statement is used to open the file. It takes the filename for Input or Output and uses the next available FileNumber.

The file is read line by line within the While loop until the file’s end is not reached: While Not EOF(FileNumber). The loop reads the data line by line and stores the data stream in the Data variable.

You can add the code statements within the While loop if you want to perform any action on the data.

Since the file is opened for Input in this program, data is read from it within the While loop. If the user opens the data in the Output mode, the following statement can be used to write the data to the file:

Write #FileNumber, "This is a sample data"

The statement "This is a sample data" is written to the file using the above command.

Use FileSystemObject With TextStream to Read a Text File Line by Line in VBA

The second solution requires a reference to Microsoft Scripting Runtime. The FileSystemObject data type is used, which allows you to read through files.

Here is a code snippet to read a file line by line:

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

Do While Not txtStream.AtEndOfStream
    txtStream.ReadLine
Loop

txtStream.Close

A FileSystemObject named fso is declared and set in the first line. The txtStream variable is a TextStream object returned when the file is opened for reading.

A TextStream object allows the user to open the file in reading, writing, or append mode.

A Do While loop is used to parse the file, line by line. The end of the file is checked by txtStream.AtEndOfStream, and the file is read using the ReadLine command.

The code snippet mentions how to read a file line by line till the end. If you want to write a line to the file, use TextStreamObject.WriteLine to output the text to the file.

At the end of the program, the file that was opened for reading is closed.

Note: The file can be opened in one mode at a time; the user cannot write and read from the same file object. Different objects have to be made to open files in different modes.

Conclusion

You can easily open a file, read from the file, write to the file, and close the file using simple VBA statements. You can use Macros to perform the same task for different files; hence, the automation power offered by Microsoft Office is incredible and applaudable.