Excel VBA: Read Text File into String (4 Effective Cases)

If you are looking for ways to read text file into a string using VBA, then this article will be helpful for you. So, let’s start with our main article to know more about this task.

Download Workbook


4 Cases to Read Text File into String Using VBA

Here, we have the following text file Geometry.txt containing some basic information about a rectangle and a circle. Using some VBA codes, we will try to read this file into a string in different ways.

VBA read text file into string

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Case-1: Using FreeFile and LOF Functions in VBA to Read Text File into String

Here, we will try to assign all of the texts in the following text file into a string variable and we will read the data of that file with the help of this string easily.

VBA read text file into string

Step-01:
➤ Go to the Developer Tab >> Visual Basic Option.

VBA read text file into string

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

FreeFile and LOF functions

After that, a Module will be created.

FreeFile and LOF functions

Step-02:
➤ Write the following code

Sub Scrutinizing_text_file_1()
Dim serial_number As Integer
Dim source_string_file, source_strings As String
source_string_file = "C:\Users\Mima\Downloads\Text\Geometry.txt"
serial_number = FreeFile
Open source_string_file For Input As serial_number
source_strings = Input(LOF(serial_number), serial_number)
Close file_number
MsgBox source_strings
End Sub

Here, we have declared serial_number as an Integer, source_string_file, and source_strings as String. Then we assigned the path of our text file to the source_string_file and serial_number to the FreeFile function which will generate the file number available for the next files.
Afterward, we tried to open our text file with the help of the Open statement and the variable source_strings will store all of the contents of that file. LOF will give us the size of our opened file and then Input will read the whole file and return us a string containing the data of this text file.
Finally, we will have the result of this string variable through a message box (MsgBox).

FreeFile and LOF functions

➤ Press F5.
After that, you will get a message box containing all of the text strings of our text file like the following.

VBA read text file into string

Read More: How to Import Text File to Excel Automatically (2 Suitable Ways)


Case-2: Using FileSystemObject Object in VBA to Read Text File into String

Here, we will read the following text file into a string with the help of the object FileSystemObject of VBA.

VBA read text file into string

Steps:
➤ Follow Step-01 of Case-1.
➤ Write the following code.

Sub Scrutinizing_text_file_2()
Dim source_strings As String
Dim new_object  As Object
Dim source_string_file As Object
Set new_object = CreateObject("Scripting.FileSystemObject")
Set source_string_file = new_object.OpenTextFile _
("C:\Users\Mima\Downloads\Text\Geometry.txt")
source_strings = source_string_file.ReadAll
source_string_file.Close
Set source_string_file = Nothing
Set new_object = Nothing
MsgBox source_strings
End Sub

Here, we have declared source_strings as String, new_object, and source_string_file as Object. To use the FileSystemObject for this code to read or do any kind of change for any file of your computer’s file system we have created this object firstly. Then using this object we are opening our text file by giving its location.
Finally, the source_strings variable will be assigned to the text strings of this file and then return the contents through a message box.

using FileSystemObject

➤ Press F5.
Finally, we will have a message box containing all of the text strings of our text file like the following figure.

using FileSystemObject

Read More: How to Import Text File to Excel Using VBA (3 Easy Ways)


Similar Readings


Case-3: Read Text File Line by Line into String and Get the String Outputs in Worksheet

In this section, we will be reading the following text file line by line and then accumulating the contents in a worksheet.

VBA read text file into string

Steps:
➤ Follow Step-01 of Case-1.
➤ Write the following code.

Sub Scrutinizing_text_file_3()
Dim serial_number As Integer
Dim source_string_file, source_strings As String
source_string_file = "C:\Users\Mima\Downloads\Text\Geometry.txt"
serial_number = FreeFile
Open source_string_file For Input As serial_number
i = 2
Do Until EOF(serial_number)
Line Input #serial_number, source_strings
Cells(i, "B").Value = source_strings
i = i + 1
Loop
Close serial_number
End Sub

Here, we have declared serial_number as an Integer, source_string_file, and source_strings as String. Then we assigned the path of our text file to the source_string_file and serial_number to the FreeFile function which will generate the file number available for the next files.
Afterward, we tried to open our text file with the help of the Open statement and set i as 2 because we want to have the output starting from Row 2.
The DO UNTIL LOOP is used to read the lines of the file until the end line reaches and up to the last line, EOF will return TRUE and so the loop will be executed till then.
We used the Line Input statement to read each line and assign them to the variable source_strings and finally we will have each line in the sheet starting from cell B2.

Reading text file line by line

➤ Press F5.
After that, we are getting all of the lines of the text file in the worksheet starting from cell B2.

VBA read text file into string

Read More: Excel VBA to Read CSV File Line by Line (3 Ideal Examples)


Case-4: Extracting Special Text Strings after Reading Text File into String

In this section, we will read the following text file line by line and then we will extract the areas of the rectangle and the circle by searching for Area1 and Area2.

VBA read text file into string

Steps:
➤ Follow Step-01 of Case-1.
➤ Write the following code.

Sub Scrutinizing_text_file_4()
Dim serial_number As Integer
Dim source_string_file, source_strings, distinct_string As String
Dim area_1, area_2 As Double
source_string_file = "C:\Users\Mima\Downloads\Text\Geometry.txt"
serial_number = FreeFile
Open source_string_file For Input As serial_number
Do Until EOF(serial_number)
Line Input #serial_number, source_strings
distinct_string = distinct_string & source_strings
Loop
Close serial_number
area_1 = InStr(distinct_string, "Area1")
area_2 = InStr(distinct_string, "Area2")
Range("B2").Value = Mid(distinct_string, area_1 + 6, 16)
Range("B3").Value = Mid(distinct_string, area_2 + 6, 20)
End Sub

Here, we have declared serial_number as an Integer, source_string_file, source_strings as String, and area_1, area_2 as Double. Then we assigned the path of our text file to the source_string_file and serial_number to the FreeFile function which will generate the file number available for the next files.
Afterward, we tried to open our text file with the help of the Open statement and set i as 2 because we want to have the output starting from Row 2.
The DO UNTIL LOOP is used to read the lines of the file until the end line reaches and up to the last line, EOF will return TRUE and so the loop will be executed till then.
We used the Line Input statement to read each line and assign them to the variable distinct_string.
The InStr function will search for the strings Area1 and Area2 and return their first positions to the variables area_1, and area_2. Finally using the MID function we will extract the value of the areas in the cells B2 and B3.

Extracting special text strings

➤ Press F5.
Eventually, you will have the area values in cells B2, and B3.

Extracting special text strings

Read More: How to Open Notepad or Text File in Excel with Columns (3 Easy Methods)


Conclusion

In this article, we tried to cover the ways to read text file into a string using VBA. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo