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

In this article, you will learn 6 related examples to read a text file line by line using VBA code in Excel.


Download Practice Workbook

You can download the Excel file from the following link and practice along with it.


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.

Read More: How to Import Text File to Excel Automatically (2 Suitable Ways)


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

Read More: How to Import Text File to Excel Using VBA (3 Easy Ways)


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 to Read CSV File Line by Line (3 Ideal Examples)


Similar Readings


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:

Read More: Excel VBA: Read Text File into String (4 Effective Cases)


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: How to Open Notepad or Text File in Excel with Columns (3 Easy Methods)


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.

Read More: Excel VBA: Import Comma Delimited Text File (2 Cases)


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 Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo