VBA read a text file

In this lesson, I’ll show you two methods you can use to read files. The first one will read a file line by line and display it on a worksheet. The second way will retrieve the specific data from the text.

Reading a file line by line

Let’s read text from the file. Open VBA Edit (Alt + F11) and insert the following code.

Sub ReadFileLineByLine()
    Dim my_file As Integer
    Dim text_line As String
    Dim file_name As String
    Dim i As Integer   

    file_name = "C:\text_file.txt"   

    my_file = FreeFile()
    Open file_name For Input As my_file   

    i = 1   

    While Not EOF(my_file)
        Line Input #my_file, text_line
        Cells(i, "A").Value = text_line
        i = i + 1
    Wend
End Sub

Code explanation

First, a new file is created from a path and the FreeFile function.

The while loop reads a line inside the file. It uses the Line Input statement to insert this line into a variable and then places it inside a worksheet. It starts from the first row (A1), then A2, A3, etc. until it reaches the end of the file.

Getting the particular values from a file

Let’s take a look at the next example.

This time we are not going to get line by line, but rather retrieve the particular data, namely:

  • First name
  • Last name
  • Age
  • Location

Then we are going to display it inside a worksheet.

In this example we asses that the data is consistent. The first line is always separated by a space and consists of two words.

Sub ReadDataFromFile()
    Dim my_file As Integer
    Dim text_line As String
    Dim file_name As String
    Dim i As Integer
    Dim name_array() As String   

    file_name = "C:\text_file2.txt"   

    my_file = FreeFile()
    Open file_name For Input As my_file   

    i = 1   

    While Not EOF(my_file)
        Line Input #my_file, text_line
        If i = 1 Then
            name_array = Split(text_line, " ")
            Cells(i, "A").Value = name_array(0)
            Cells(i + 1, "A").Value = name_array(1)
        ElseIf i = 2 Then
            pos = InStr(text_line, "Age: ")
            If pos <> 0 Then
                Cells(i + 1, "A").Value = Replace(text_line, "Age: ", "")
            End If
        ElseIf i = 3 Then
            pos = InStr(text_line, "Location: ")
            If pos <> 0 Then
                Cells(i + 1, "A").Value = Replace(text_line, "Location: ", "")
            End If
        End If
        i = i + 1
    Wend
End Sub

Code explanation

This loop, similarly to the previous one, is repeated until it gets to the end of the file. But there is little more that is going inside this loop.

The first IF checks whether we are inside the first line. If it’s true, it splits the file into the First name (name_array(0)) and the Last name (name_array(1)).

Then, there is the second IF, which checks whether we are inside the second line of the file. If it’s true, the InStr checks the position of the string “Age: ” inside the text_line variable. If the position is not 0, that means the string exists in this line. Now, the Replace function replaces “Age: ” to “”. In other words, it removes “Age: ”.

The similar situation is inside the third IF. This time for “Location: “, instead of “Age: ”.

And this is the final result.

Posted in vba