Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

How to Read Data From Text File in Excel VBA?

  • 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:
VariablesData TypeComments
lineStringRead text file line by line
FilenameStringInput file name (Full path)
iIntegerIterator
valueArr()Stringsplit 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!