Excel VBA: Read a Text File Line by Line (6 Related Examples)

To demonstrate our examples, we’ll use the following text file saved in the Desktop folder.

Text File to Read Line by Line Using VBA in Excel

Example 1 – Read First Line of a Text File

This code will read only the first line of a text file.

Steps:

Press ALT + F11 to open the VBA Editor.
Go to Insert >> Module.

Insert a Module to Read First Line of a Text File Using VBA in Excel

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

Read First Line of a Text File Using VBA in Excel

Breakdown of the Code

We declare two variables.
The FilePath property is used to fetch the text file.
The Open and Line Input properties read the text file.
A message box displays the output.

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.

Example 2 – Read an Entire Text File Line by Line

The following code will read an entire text file line by line and store the fetched text lines in your spreadsheet.

Open the VBA Editor.
Insert and Save the following VBA code:

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

Read an Entire Text File Line by Line Using VBA in Excel

Breakdown of the Code

We declare two variables.
The xFile variable is used to fetch the text file.
The Open and Line Input properties read the text file.

Press the F5 key to Run the code.

The output appears in your worksheet like this:

Output: Read an Entire Text File Line by Line Using VBA in Excel

Example 3 – Separate All Lines with Delimiter when Reading an Entire Text File Line by Line

If you have delimiters in your texts and want to use them to split your texts, use this code.

Open the VBA Editor.
Insert and Save the following VBA code:

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

Separate all Lines by Delimiter by Reading an Entire Text File Line by Line Using VBA in Excel

Breakdown of the Code

We declare seven variables.
The OpenTextFile property opens the text file.
The variable xDelimiter sets semicolons as delimiter.
We use Early Binding. There are two types of binding, Early Binding and Late Binding. Early Binding is comparatively faster.
A Do While loop splits all the texts by the delimiter.

Press the F5 key to Run the code.

If your code doesn’t Run, then:

Go to Tools >> References.

Select Microsoft Scripting Runtime and click OK.

Manage Settings to Separate all Lines by Delimiter by Reading an Entire Text File Line by Line Using VBA in Excel

The code reads the text file line by line and splits them by the delimiter.

Example 4 – Open a Text File with a Prompt

Here we open a text file using a prompt window.

Open the VBA Editor.
Insert and Save the following code:

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

Open a Text File with a Prompt and Read Text File Line by Line Using VBA

Breakdown of the Code

We declare four variables.
The FileDialog opens a prompt window.
The Open and Line Input properties inside the IF statement and While loop read the text files line by line.

Press the F5 key to Run the code.
Select your text file from the prompt window.
Click Open.

Prompt Window: Open a Text File with a Prompt and Read Text File Line by Line Using VBA

The text file will open, and the text will be extracted line by line:

Example 5 – Check Errors and Read an Entire Text File

This code checks for errors while reading the text file. If it finds any, a dialog box appears and shows “Error detected!”.

Open the VBA Editor.
Insert and Save the following code:

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

Check Errors and Read an Entire Text File Line by Line Using VBA in Excel

Breakdown of the Code

We declare four variables.
The FileDialog property opens a prompt window.
The Open and Line Input properties inside the IF statement and a While loop read the text files line by line.
The lblError property checks for errors. If any error occurs, a message box shows “Error detected!”.

Press F5 to Run the code.
Select your text file from the prompt window.
Click Open.

The text file will open and the code will read and extract the text.

Example 6 – Read Specific Number of Lines from a Text File

The code used here can manually set the number of lines to read and extract.

Open the VBA Editor.
Insert and Save the following code:

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

Read Specific Number of Lines from a Text File Using VBA in Excel

Breakdown of the Code

We declare seven variables.
The variable xfileName fetches the text file.
The two variables xLine1 and noLines1 set the number of lines to read line by line.
The Line Input property inside the Do While loop reads and extracts text from the file line by line.