Skip to content
Related Articles

Related Articles

How to Read Data From Text File in Excel VBA?

View Discussion
Improve Article
Save Article
  • Last Updated : 29 Oct, 2021

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
Recommended Articles
Page :

Start Your Coding Journey Now!