Read/Parse a Text file line by line using VBA

There are a couple of inbuilt features in Excel to import data from text files 

• Import Text Data (Delimited) to Excel
• Import Text Data (Fixed length) to Excel

In this tutorial, I will show how to read text file line by line using VBA which can prove useful to cater some special conditions based on which you may want to import the data to Excel. In theory you need to follow the below steps

1. Open a file
2. Assign a file number to it
3. Read the file line by line 
4. Close the file. The will free the file so that other applications can use it.

As discussed in the previous tutorial here, a developer  tab can be enabled in Excel which can then be used to do various tasks. On the same lines let me explain, how to write a Macro which will Open a text file and read the contents from it. 

To begin with, go to "Developer" tab and click on "View Code". This will open a window where  you can write some VBA code. Insert a new module to the workbook as show below:

Now copy and paste the below code into it

Sub ReadTextFile()

    Dim sBuffer As String
    Dim sFileName As String
    Dim iFileNumber As Integer

    'File Path
    sFileName = "C:\temp\test.txt"

    ' Check if the file exists
    If Len(Dir$(sFileName)) = 0 Then
        MsgBox ("File does not Exist")
        Exit Sub
    End If
    
    'Assign a number which is not used yet
    iFileNumber = FreeFile()
    
    'Open the file and assign the number allocated to it
    Open sFileName For Input As iFileNumber

    'Loop through the file line by line until
    'end of file is reached
    Do While Not EOF(iFileNumber)
        Line Input #iFileNumber, sBuffer
        
        'Insert you logic to read and filter data here
        
    Loop

    'Close the file
    Close iFileNumber

End Sub

The above code will read the code line by line. You can insert you logic in between to process the line read. You can also download the sample code here.