How to use VBA to read Data from a Text file?

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)

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.

VBA Script

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

1. Add a shape Read Data File to your worksheet.

2. Right-click on the Read Data file button and choose Assign Macro…
3. Select ReadTextFile Macro from the list of macros in the Assign Macro dialog box
4. Save your excel file as Excel Macro-Enabled Workbook  *.xlsm
5. Click the button Read Data file
6. 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.