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)
VBA code will read a text file and places on worksheet cells as below
- Declaring variables:
|line||String||Read text file line by line|
|Filename||String||Input file name (Full path)|
|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
- Close file
'Close file Close #2
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.