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.

Jump To:


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:

Excel VBA, Text File to Read first line
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:

Excel VBA, Read Text File Example 1 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:

Excel VBA Run-time error '55', File Already Open

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:

Excel VBA, text file to read all 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:

Excel VBA, Read Text File Results


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:

Excel VBA, Read Text File Using Dialog

Result:

Excel VBA, Text File to Read Dialog Resultpng


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:

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

One thought on “Excel VBA, Reading Text Files”

  1. Jay says:

    Hello,

    Can you help me to get the Macro code for the below requirement

    input Excel

    database : aaaaa
    Column : data
    Status Format Count Total Rows% Sample
    valuea aaaaa 45644 100 10/16/2010
    Database : bbbbb
    Column : name
    Status Format Count TotalRows% Sample
    valueb bbbbb 34355 100 sfdhdsffd
    ….
    …..
    I have n number of similar type of data set in the same excel sheet

    required output Excel

    Database Column Format Count TotalRows% Sample
    aaaaa data aaaaa 45644 100 10/16/2010
    bbbbb name bbbbb 34355 100 sfdhdsffd

Leave a Reply

Your email address will not be published. Required fields are marked *



© Copyright 2017 software-solutions-online.com.