Excel VBA, Reading Text Files
In this article I will explain how you can read data from a text file. You can download the workbook and code for this article here.
- Example 1, Opening a Text File and Reading the First Line
- Example 2, Closing the Text File After Using it
- Example 3, Reading All Lines of Text From a Text File
- Example 4, Selecting Text File With Dialog
- Example 5, Checking for Errors When Opening the Text File
In this example a text file with the path “D:test.txt” will be openend and the first line will be displayed in a message box. The text file has the following data in it:
The first line of code sets the value of FilePath to the full path of the text file:
The line below opens the file at the specified location and assigns the index “1” to it:
The full path of the text file to to open
States that we are only going to use this text file for reading data.
This would be the reference to the file we have opened. Instead of “1” we could have used any number between 1 and 511. Usually the number #1 is always used unless if we are attempting to read from multiple files at the same time.
The line below reads the first line of data from the file with the index “1” and assigns the value to the variable strFirstLine:
The index of the file to read the first line from
The variable to assign the data from the first line of the text file to.
The problem with Example 1 was that it didn’t close the file after using it. If you try to run the code in example 1 a second time you will get the following error:
In order to prevent this error you would need to close the file after using it. This can be done by adding the following line to the code in Example 1:
Close #1 closes the file with the “1′ index. Had we used another number while opening the file the same number should be used when closing the file:
Instead of using the index “1” we could have used the index “2”:
The following code opens the text file located at the path “D:test2.txt” and prints every line in a separate cell. The file test2.txt contains the following data:
The following code loops through all the lines in the text file and prints them in column A:
In the example below the text file is selected using an open file dialog. All the lines of data from the text file are read and printed in column A. For more information about the open file dialog please see Excel VBA Open File Dialog:
The file chosen had the following data in it:
When opening a text file several things could go wrong which could cause the program to crash. It’s always a good idea to add an exception handler to handle such cases. For more information on this issue please see VBA, Check if Text File is Open. The code below asks the user to select a file to open. If the file is opened successfully all the data in the text file printed in column A. If there is an error while opening the file, a message box is displayed:
You can download the workbook and code for this article here.
- VBA Excel Open File Dialog
- VBA, Modify Existing Text File
- VBA Excel, Writing to a Text File
- VBA, Check if Text File is Open
- Find and List All Files and Folders in a Directory