In this article, you will learn 6 related examples to read a text file line by line using VBA code in Excel.
6 Examples to Read a Text File Line by Line Using VBA in Excel
I’ve used the following text file saved in the Desktop folder. I’ve used the VBA codes to read this text file line by line in Excel.
1. Read First Line of a Text File Using VBA in Excel
The code used in this method can read only the first line of a text file.
❶ First of all, press ALT + F11 to open the VBA Editor.
❷ Then go to Insert ➤ Module.
❸ Insert and Save the following VBA code in the VBA Editor.
Sub Read_First_Line()
Dim FilePath As String
Dim xFirstLine As String
FilePath = "C:\Users\user\Desktop\New Text Document.txt"
Open FilePath For Input As #1
Line Input #1, xFirstLine
MsgBox (xFirstLine)
End Sub
Breakdown of the Code
- At first, I have declared two variables.
- Then I used the FilePath property to fetch the text file.
- After that, I used the Open and Line Input property to read the text file.
- Finally, I used a message box to display the output.
❹ Now press the F5 key to Run the above code.
This code will read only the very first line of the text file and the result will be displayed in a MsgBox.
2. Read an Entire Text File Line by Line Using VBA in Excel
The following code can read an entire text file line by line. It stores the fetched text lines in your Excel spreadsheet.
Now, click_here to learn to open the VBA Editor.
❶ Insert and Save the following VBA code in the VBA Editor.
Sub Read_Entire_Text_File()
Dim xFile As String
Dim xLine As String
xFile = "C:\Users\user\Desktop\New Text Document.txt"
Open xFile For Input As #1
Do Until EOF(1)
Line Input #1, xLine
ActiveCell = xLine
ActiveCell.Offset(1, 0).Select
Loop
Close #1
End Sub
Breakdown of the Code
- At first, I have declared two variables.
- Then I used the xFile variable to fetch the text file.
- After that, I used the Open and Line Input property to read the text file.
❷ Then press the F5 key to Run the code.
The above code will read the text file line by line and it will store the output in your Excel worksheet like this:
3. Separate All Lines by Delimiter by Reading an Entire Text File Line by Line Using VBA in Excel
If you have delimiters in your texts and want to split your texts by delimiter, then you can use this code.
Now, click_here to learn to open the VBA Editor.
❶ Insert and Save the following VBA code in the VBA Editor.
Sub Read_and_Separate_by_Delimiter()
Dim xLine As String
Dim xFSO As FileSystemObject
Dim xTSO As Object
Dim xLineElements1 As Variant
Dim xIndex As Long
Dim z As Long
Dim xDelimiter As String
Set xFSO = CreateObject("Scripting.FileSystemObject")
Set xTSO = xFSO.OpenTextFile("C:\Users\user\Desktop\New Text Document(2).txt")
xDelimiter = ";"
xIndex = 1
Do While xTSO.AtEndOfStream = False
xLine = xTSO.ReadLine
xLineElements1 = Split(xLine, xDelimiter)
For z = LBound(xLineElements1) To UBound(xLineElements1)
Cells(xIndex, z + 1).Value = xLineElements1(z)
Next z
xIndex = xIndex + 1
Loop
xTSO.Close
Set xTSO = Nothing
Set xFSO = Nothing
End Sub
Breakdown of the Code
- At first, I have declared seven variables.
- Then I used the OpenTextFile property to open the text file.
- I used the variable, xDelimiter to consider semicolons as a delimiter.
- In this code, I used Early Binding. Basically, there are two types of binding. Early Binding and Late Binding. Early Binding is comparatively faster than Late Binding.
- After that, I used a Do While loop to split all the texts by delimiter.
❷ Press the F5 key to Run the VBA code.
If your code doesn’t Run, then
❸ Go to Tools ➤ References.
❹ Select Microsoft Scripting Runtime and hit OK.
The code will read your text file line by line and split them by delimiters.
Read More: Excel VBA: Read Text File into String
4. Open a Text File with a Prompt Using VBA
The VBA code used in this method can open a text file using a prompt window and read your text file line by line.
Now, click_here to learn to open the VBA Editor.
❶ Insert and Save the following VBA code in the VBA Editor.
Sub Open_Text_File_Using_Prompt()
Dim xLine As String
Dim z As Integer
Dim xResult As Integer
Dim xPath As String
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
xResult = Application.FileDialog(msoFileDialogOpen).Show
If xResult <> 0 Then
xPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Open xPath For Input As #1
z = 1
While EOF(1) = False
Line Input #1, xLine
Cells(z, 1) = xLine
z = z + 1
Wend
End If
Close #1
End Sub
Breakdown of the Code
- At first, I have declared four variables.
- Then I used FileDialog property to open a prompt window.
- After that, I used the Open and Line Input property inside the IF statement and a While loop to read the text files line by line.
❷ Now press the F5 button to Run the code.
❸ Select your text file from the prompt window.
❹ Then hit Open.
Your text file will open up and the code will read and extract your text line by line as follows:
5. Check Errors and Read an Entire Text File Using VBA in Excel
This code can also read an entire text file line by line. But this code does an extra job. This is it checks for errors while opening the text file.
If it finds any errors, then a dialog box appears and shows “Error detected!”.
Now, click_here to learn to open the VBA Editor.
❶ Insert and Save the following VBA code in the VBA Editor.
Sub Check_Errors_and_Read()
Dim xLine As String
Dim z As Integer
Dim xResult As Integer
Dim xPath As String
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
xResult = Application.FileDialog(msoFileDialogOpen).Show
If xResult <> 0 Then
xPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Open xPath For Input As #1
z = 1
While EOF(1) = False
Line Input #1, xLine
Cells(z, 1) = xLine
z = z + 1
Wend
End If
Close #1
Exit Sub
lblError:
MsgBox ("Error detected!")
Err.Clear
Close #1
End Sub
Breakdown of the Code
- At first, I have declared four variables.
- Then I used FileDialog property to open a prompt window.
- After that, I used the Open and Line Input property inside the IF statement and a While loop to read the text files line by line.
- Then I used the lblError property to check where an error occurs. If any error occurs then I’ve used a message box to show “Error detected!”.
❷ Now press the F5 button to Run the code.
❸ Select your text file from the prompt window.
❹ Then hit Open.
Your text file will open up and the code will read and extract your text line by line as follows:
Read More: Excel VBA: Import Comma Delimited Text File
6. Read Specific Number of Lines from a Text File Using VBA in Excel
The code used in this method can read a specific number of lines from your text file. You can manually set the number of lines to read and extract.
Now, click_here to learn to open the VBA Editor.
❶ Insert and Save the following VBA code in the VBA Editor.
Option Explicit
Sub Read_Specific_Lines()
Dim xfileName As String
Dim xtextData As String
Dim xtextRow As String
Dim xfileNo As Integer
Dim xlineCount As Long
Dim xLine1 As Long
Dim noLines1 As Long
xfileName = "C:\Users\user\Desktop\New Text Document.txt"
xLine1 = 1
noLines1 = 5
xfileNo = FreeFile
Open xfileName For Input As #xfileNo
Do While Not EOF(xfileNo)
Line Input #xfileNo, xtextRow
If xlineCount >= xLine1 And ((noLines1 > 0 And xlineCount < noLines1 + xLine1) Or noLines1 = 0) Then
xtextData = xtextData & xtextRow
ActiveCell = xtextRow
ActiveCell.Offset(1, 0).Select
End If
xlineCount = xlineCount + 1
Loop
Close #xfileNo
End Sub
Breakdown of the Code
- At first, I have declared seven variables.
- Then I used a variable xfileName to fetch the text file.
- After that, I used two variables xLine1 and noLines1 to set the number of lines to read line by line.
- I used the Line Input property inside the Do While loop to read and extract text files line by line.
❷ Now press the F5 button to run the VBA code.
The code will start to read from line 1. It will extract in total 5 lines from lines 1 to 5.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 6 examples to read a text file line by line using VBA in Excel. You are recommended to download the practice workbook attached with this article and practice all the methods. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website ExcelDemy to explore more.
