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.
You can find following syntax to Read Data to Text File in Excel VBA.
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.
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
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.
Here are the instructions to use above macro in Visual basic editor.
- Open Visual Basic Editor(VBE) by clicking Alt +F11
- Go to code window by clicking F7
- Copy above specified macro or procedure
- Paste above copied code in code window
- Run macro by clicking F5 or Run command
- You can see output in the immediate window
- you can find above specified input and output screenshot for your reference and better understand.
You can also learn complete details like syntax, example and etc.