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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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, 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.
