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 the text from the file. Open VBA Edit (Alt + F11) and insert the following code.

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.

Code explanation

This loop, similar to the previous one, is repeated until it gets to the end of the file. But there is little more that is going on 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, InStr checks the position of the string “Age: ” inside the text_line variable. If the position is not 0, the string exists in this line. Now, the Replace function replaces “Age: ” to “”. In other words, it removes “Age: ”.

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

And this is the final result.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.