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.
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.
Step-01:
➤ Go to the Developer Tab >> Visual Basic Option.
Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.
After that, a Module will be created.
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 SubHere, 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).
➤ Press F5.
After that, you will get a message box containing all of the text strings of our text file like the following.
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.
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 SubHere, 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.
➤ Press F5.
Finally, we will have a message box containing all of the text strings of our text file like the following figure.
Read More: How to Import Text File to Excel Using VBA (3 Easy Ways)
Similar Readings
- Excel VBA to Import CSV File without Opening (3 Suitable Examples)
- How to Import CSV into Existing Sheet in Excel (5 Methods)
- Excel VBA: Import Comma Delimited Text File (2 Cases)
- How to Convert CSV to XLSX (4 Quick Methods)
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.
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 SubHere, 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.
➤ Press F5.
After that, we are getting all of the lines of the text file in the worksheet starting from cell B2.
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.
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 SubHere, 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.
➤ Press F5.
Eventually, you will have the area values in cells B2, and B3.
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.
