How to Read Data From Text File in Excel VBA

VBA Program to read a Text file line by line (Sales Data) and place it on a worksheet It provides a concise introduction to the process of importing data from text files into Microsoft Excel using Visual Basic for Applications (VBA). This introductory article serves as a starting point for individuals who want to automate the task of reading and processing data from external text files within an Excel workbook. It likely covers essential concepts such as variable declarations, file handling, and data extraction, setting the stage for readers to delve deeper into the specifics of VBA coding to accomplish this task efficiently. Overall, it offers readers a fundamental understanding of the topic and hints at the practical benefits of utilizing VBA for data manipulation in Excel.

Sales Data in Text File: 5 Fields [ Product, Qtr 1, Qtr 2, Qtr 3 and Qtr 4 ] and 25 Records (incl. header)

Sales data

VBA code will read a text file and place it on worksheet cells as below

VBA – Read Text File Line by Line

Declaring variables

Variables	Data Type	Comments
line	String	Read text file line by line
Filename	String	Input file name (Full path)
i	Integer	Iterator
value()	String	split the sentence by comma and store it in an array variable of type String

‘Variable declarations

Dim line As String, Filename As String, i As Integer, valueArr() As String

Initialize the “Filename” variable with full path and filename

‘Text file fullPath
Filename = “D:\Excel\ReadTextFile\sales.txt” ‘update your full file path
i = 1

Open the input file to read the text 

‘Open file
Open Filename For Input As #2

Read input file line by line

‘Read line by line – text file
While Not EOF(2)
Line Input #2, line

• Split by a comma and store it in valuer ().  In our example, each line has 5 values concatenated with a comma.

‘split the line by comma separated, assigned in an array
valuesArr() = Split(line, “,”)

• Add text to respective cells from values ().  Read each item in an array by its index value

Cells(i, “A”).Value = valuesArr(0)
Cells(i, “B”).Value = valuesArr(1)
Cells(i, “C”).Value = valuesArr(2)
Cells(i, “D”).Value = valuesArr(3)
Cells(i, “E”).Value = valuesArr(4)

Increment counter i, to move next line.

i = i + 1

• Close while loop

Wend

Close file

‘Close file
Close #2

How to Read Text File Line by Line

Step 1: Open Excel

Step 2: Add a Shape

Add a shape (Read Text File) to your worksheet.

Step 3: Right Click and Select Assign Macro

Right-click on “Read Text file” and “Assign Macro..”

Step 4: Select ReadTextFileLineByLine Macro

Step 5: Save your Excel File

Save your Excel file as “Excel Macro-Enabled Workbook”  *.xlsm

Step 6: Click Read Text file 

Step 7: Adjust the Column Width

Adjust the column width in your Excel file.

How to Read Text Files in Arrays in Excel

In Excel VBA, you can read text files into arrays by following these steps:

Step 1: Open Excel and Press ALT + F11 to Open the VBA Editor

Step 2: Insert a New Module

Insert a new module by clicking “Insert” > “Module.”

Insert a Module

Step 3: Enter the Below code in VBA window

Note: Please replace “C:\YourFolderPath\YourFile.txt” with the actual path of the text file you want to read. This code will read the lines from the text file and store them in the TextArray. It also includes an example of how to print the contents of the array to the Immediate Window for testing purposes. You can modify the code to perform any desired operations on the data stored in the array.

Step 4: Now Click on Run Module

FAQs

What is the purpose of reading data from a Text file in Excel VBA?

Reading data from a text file using Excel VBA allows you to import external data into your Excel workbook. This can be useful for data analysis, reporting, and integration of data from various sources.

How to open the Visual Basic for Applications (VBA) editor in Excel?

Press ‘Alt’ + ‘F11’ to open the VBA within Excel. This will bring up the VBA development environment where you can write and edit VBA code.

What is the SteamReader object in Excel VBA used for?

The SteamReader object is used to read data from a text file in a sequential manner. It provides methods for reading lines, characters, and other data from the file.

What are the limitations or considerations when reading data from a text file in Excel VBA?

Careful of memory usage when reading large text files into Excel, as excessive data can slow down your workbook or cause memory-related issues.