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.

Jump To:

• 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

Example 1, Opening a Text File and Reading the First Line:

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:

This can be achieved by the code below:

Sub Example1()
Dim FilePath As String
Dim strFirstLine As String

FilePath = "D:test.txt"
Open FilePath For Input As #1
Line Input #1, strFirstLine
MsgBox (strFirstLine)
End Sub


Result:

The first line of code sets the value of FilePath to the full path of the text file:

FilePath = "D:test.txt"


The line below opens the file at the specified location and assigns the index “1” to it:

Open FilePath For Input As #1


FilePath:

The full path of the text file to to open

Input:

States that we are only going to use this text file for reading data.

1:

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:

Line Input #1, strFirstLine


1:

The index of the file to read the first line from

strFirstLine:

The variable to assign the data from the first line of the text file to.

Example 2, Closing the Text File After Using It:

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


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:

Sub Example2()
Dim FilePath As String
Dim strFirstLine As String
FilePath = "D:test.txt"
Open FilePath For Input As #1
Line Input #1, strFirstLine
MsgBox (strFirstLine)
Close #1
End Sub


Instead of using the index “1” we could have used the index “2”:

Sub Example3()
Dim FilePath As String
Dim strFirstLine As String
FilePath = "D:test.txt"
Open FilePath For Input As #2
Line Input #2, strFirstLine
MsgBox (strFirstLine)
Close #2
End Sub


Example 3, Reading All Lines of Data in a Text File:

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:

Sub Example4()
Dim FilePath As String
Dim strLine As String
Dim i As Integer

FilePath = "D:test2.txt"
Open FilePath For Input As #1
i = 1
While EOF(1) = False
    'read the next line of data in the text file
    Line Input #1, strLine
    'print the data in the current row
    Cells(i, 1) = strLine
    'increment the row counter
    i = i + 1
Wend
Close #1
End Sub


Result:

Example 4, Opening a Text File With a Dialog:

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:

Sub Example5()

Dim strLine As String
Dim i As Integer
Dim intResult As Integer
Dim strPath As String

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intResult = Application.FileDialog(msoFileDialogOpen).Show
If intResult <> 0 Then
    strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Open strPath For Input As #1
    i = 1
    While EOF(1) = False
        'read the next line of data in the text file
        Line Input #1, strLine
        'print the data in the current row
        Cells(i, 1) = strLine
        'increment the row counter
        i = i + 1
    Wend
End If
Close #1
End Sub


The file chosen had the following data in it:

Result:

Example 5, Checking for Errors When Opening the Text File:

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:

Sub Example6()
Dim strLine As String
Dim i As Integer
Dim intResult As Integer
Dim strPath As String

Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
intResult = Application.FileDialog(msoFileDialogOpen).Show
If intResult <> 0 Then
    strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
    On Error GoTo lblError:
    Open strPath For Input As #1
    i = 1
    While EOF(1) = False
        'read the next line of data in the text file
        Line Input #1, strLine
        'print the data in the current row
        Cells(i, 1) = strLine
        'increment the row counter
        i = i + 1
    Wend
End If
Close #1
Exit Sub

lblError:
MsgBox ("There was an error opening the file. Implement the necessary actions")
Err.Clear
Close #1
End Sub


You can download the workbook and code for this article here.

See also:

• 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

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com