VBA Read Text Files

VBA Read Text Files using the Input Statement or the Line Input statement. The Input statement expects data that generates by Write and reads data into a list of variables. The Line Input statement reads the whole line of data as a single string variable. We can read text files either line by line or all lines. After Read Text files data and store into string, array, or Worksheet. Let us see different examples in the following tutorial.

Table of Contents:

Objective
Syntax to Read Text File
VBA Read Text File Line by Line using Line Input Statement
VBA Read Whole Data from Text File into String using Input Statement
Instructions to Run VBA Macro Code
Other Useful Resources

Syntax to Read Text File

You can find following syntax to Read Data to Text File in Excel VBA.
Syntax:

Input #FileNumber, Input_Value1, Input_Value2, ...
'or
Line Input #FileNumber, Line_Input_String

Where FileNumber represents the unique number to file and contains numeric value. It helps to open text files.
Input_Value1, Input_Value2, … represents the input data which you want to write to text file. All values are separated by commas, and it adds hash marks(#) around dates and quotes(“) around strings while writing to text file.
and Line_Input_String represents the input string to write to text file.

VBA Read Text File Line by Line using Line Input Statement

You can find following VBA Code. It helps to Read all lines data from Text File Line by Line in Excel.

'VBA Read Text File Line by Line
Sub VBAF1_Read_Text_File_Line_by_Line()

    'Variable Declaration
    Dim sFilePath As String
    Dim strTextLine As String
    Dim iFile As Integer
    
    'Specify Text File Path
    sFilePath = "C:\VBAF1\FirstTextFile.txt"
    
    'Get Unique File Number using FreeFile
     fileNumber = FreeFile
    
    'Open Text File to Read Data
    Open sFilePath For Input As #fileNumber
    
    'Read Whole data in text file
    Do Until EOF(1)
        'Read data line by line
        Line Input #1, strTextLine
    Loop
    
    'Close Text File
    Close #fileNumber

End Sub

VBA Read Whole Data from Text File into String using Input Statement

You can find following VBA Code. Here we use Input statement to retrieve data. It helps to Read data from Text File into string variable in Excel. We are displaying output on the screen.

'VBA Read Text File into String Variable
Sub VBAF1_Read_Text_Into_String()

    'Variable Declaration
    Dim fileName As String, sVariableData As String, fileNumber As Integer
    
    'Specify Text File Path
    sFilePath = "C:\VBAF1\FirstTextFile.txt"
    
    'Get Unique File Number using FreeFile
     fileNumber = FreeFile
     
     'Open Text File to Read Data
    Open sFilePath For Input As #fileNumber
    
    'Store Data into a string variable
    sVariableData = Input$(LOF(fileNumber), #fileNumber)
    
    'Display output on the screen
    MsgBox sVariableData, vbInformation, "VBAF1"
    
    'Close Text File
    Close #fileNumber
    
End Sub

Output: You can find following Input and output screenshot for your reference.