Do you need to read text file content using excel VBA? This article will show you step by step how to use VBA to read data from a text file and paste it into workbooks.
You may receive the data in a text file with the number of rows and separated by any one of the delimiters (Comma, Semicolon, Tab, Special character, etc)
Suppose you have the below text in your text data file and need to read data from it into your worksheet.
Country Sales Table in Text File: 3 Fields [ Country, Sales, Profit ] and 10 Records (incl. header)
![Data Text file - to read Data from a Text file](https://i0.wp.com/pptexcel.com/wp-content/uploads/2022/10/a004_001.png?resize=275%2C287&ssl=1)
VBA script will read the sales table from your text file and place it on the worksheet. Your final output looks like the below image.
![Output Image - to read Data from a Text file](https://i0.wp.com/pptexcel.com/wp-content/uploads/2022/10/a004_002.png?resize=411%2C334&ssl=1)
VBA Script
![Variable Declaration table](https://i0.wp.com/pptexcel.com/wp-content/uploads/2022/10/a004_003-2.png?resize=554%2C123&ssl=1)
![VBA Script](https://i0.wp.com/pptexcel.com/wp-content/uploads/2022/10/a004_004.png?resize=657%2C447&ssl=1)
Sub ReadTextFile()
'Declare Variables
Dim records As String, fileName As String, i As Integer, valArr() As String
'data file fullPath
fileName = "D:\Projects\xl\pptExcel\ReadTextFile\salesData.txt"
i = 1
Open fileName For Input As #2
'Read record from a text file
While Not EOF(2)
Line Input #2, records
'split the record by comma separated and assigned it to an array variable
valArr() = Split(records, ",")
Cells(i, "A").Value = valArr(0)
Cells(i, "B").Value = valArr(1)
Cells(i, "C").Value = valArr(2)
i = i + 1
Wend
'Close file
Close #2
End Sub
Copy the above VBA Script and Paste it into your VBA module
If you have any other delimiters in your data file you can update the below line of code. Used the below code since you have a comma separator in our data file.
valArr() = Split(records, ",")
Please follow the below steps to assign the above macro to your worksheet button
![](https://i0.wp.com/pptexcel.com/wp-content/uploads/2022/10/a004_005.png?resize=546%2C144&ssl=1)
- Add a shape Read Data File to your worksheet.
![Assign macro to a shape](https://i0.wp.com/pptexcel.com/wp-content/uploads/2022/10/a004_006.png?resize=450%2C518&ssl=1)
- Right-click on the Read Data file button and choose Assign Macro…
- Select ReadTextFile Macro from the list of macros in the Assign Macro dialog box
- Save your excel file as Excel Macro-Enabled Workbook *.xlsm
- Click the button Read Data file
- Adjust the column width to cover the entire text content in your worksheet.
Conclusion
Reading and writing a text file is a basic operation for data analysis. In excel you can simply open your data file and copy the entire data, and paste it into your worksheet manually. You can use VBA Script If you need to automate the process to read data from a text file into a worksheet.