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.