VBA – Read Text File Line by Line
Declaring variables
| Variables | Data Type | Comments |
|---|---|---|
| line | String | Read text file line by line |
| Filename | String | Input file name (Full path) |
| i | Integer | Iterator |
| value() | 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, valueArr() As String
Initialize the “Filename” variable with full path and filename
‘Text file fullPath
Filename = “D:\Excel\ReadTextFile\sales.txt” ‘update your full file path
i = 1
Open the input file to read the 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 a comma and store it in valuer (). In our example, each line has 5 values concatenated with a comma.
‘split the line by comma separated, assigned in an array
valuesArr() = Split(line, “,”)
- Add text to respective cells from values (). Read each item in an array by its 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
How to Read Data From Text File in Excel VBA
VBA Program to read a Text file line by line (Sales Data) and place it on a worksheet It provides a concise introduction to the process of importing data from text files into Microsoft Excel using Visual Basic for Applications (VBA). This introductory article serves as a starting point for individuals who want to automate the task of reading and processing data from external text files within an Excel workbook. It likely covers essential concepts such as variable declarations, file handling, and data extraction, setting the stage for readers to delve deeper into the specifics of VBA coding to accomplish this task efficiently. Overall, it offers readers a fundamental understanding of the topic and hints at the practical benefits of utilizing VBA for data manipulation in Excel.
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 place it on worksheet cells as below
Contact Us