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.

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 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

• 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

• Press the F5 key to Run the code.

The output appears in your worksheet like this:

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

• 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.

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

Read More: Excel VBA: Read Text File into String

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

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

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

• 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

• Press the F5 key to run the code.

The code will extract lines 1 to 5.

Download Practice Files

Related Article:

• How to Import Text File to Excel Using VBA