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

Get FREE Advanced Excel Exercises with Solutions!

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.

Text File to Read Line by Line Using VBA 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.

Here’s how to use the code.

❶ First of all, press ALT + F11 to open the VBA Editor.

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

  • 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

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

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:

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


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

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

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.

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

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

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

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.

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

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

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

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

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

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.


Related Article:

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo