How to Read Data From Text File in Excel VBA?

VBA Program to read a Text file line by line (Sales Data) and places on a worksheet. 

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 places on worksheet cells as below

VBA Code:

• Declaring variables:

Variables	Data Type	Comments
line	String	Read text file line by line
Filename	String	Input file name (Full path)
i	Integer	Iterator
valueArr()	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, valuesArr() As String

• Initialize “Filename” variable with full path and filename

    'Text file fullPath
    Filename = "D:\Excel\ReadTextFile\sales.txt" 'update your full file path
    i = 1

• Open input file to read 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 comma and store it in valueArr().  In our example, each line has 5 values concatenated with comma.

        'split the line by comma separated, assigned in an array
        valuesArr() = Split(line, ",")

• Add text to respective cells from valuesArr().  Read each item in an array by it’s 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

Approach:

Step 1: Open Excel.

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

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

Step 4: Select ReadTextFileLineByLine Macro

Step 5: Save your excel file as “Excel Macro-Enabled Workbook”  *.xlsm

Step 6: Click “Read Text file” 

Step 7: Adjust column width in your excel file.

My Personal Notes arrow_drop_up

Save