Excel 2010 Macro Read One Row at a Time from a Text File
An alternative is to read text file one row at a time. Open the file for INPUT as #1. Then use the Line Input #1 statement to read a line of the file into a variable. The following code opens sales.txt, reads 10 lines of the file into the first 10 cells of the worksheet, and closes the file:
Sub Import10()
ThisFile = “C\sales.txt”
Open ThisFile For Input As #1
For i = 1 To 10
Line Input #1, Data
Cells(i, 1).Value = Data
Next i
Close #1
End Sub
Instead of reading only 10 records, you’ll want to read until the end of the file. Excel automatically updates a variable called EOF. If you open a file for input as #1, check EOF(1) and it will tell you whether you have read the last record. Use a Do...While loop to keep reading records until you reach the end of the file:
Sub ImportAll()
ThisFile = “C:\sales.txt”
Open ThisFile For Input As #1
Open ThisFile For Input As #FileNumber
Ctr = 0
Do
Line Input #FileNumber, Data
Ctr = Ctr + 1
Cells(Ctr, 1).Value = Data
Loop While EOF(FileNumber) = False
Close #FileNumber
Cells(1, 1).Resize(Ctr, 1).TextToColumns Destination:=Range(“A1”), _
DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, xlGeneralFormat), _
Array(2, xlMDYFormat), Array(3, xlGeneralFormat), _
Array(4, xlGeneralFormat), Array(5, xlGeneralFormat), _
Array(5, xlGeneralFormat), Array(6, xlGeneralFormat), _
Array(7, xlGeneralFormat), Array(8, xlGeneralFormat), _
Array(9, xlGeneralFormat), Array(10, xlGeneralFormat), _
Array(10, xlGeneralFormat), Array(11, xlGeneralFormat))
End Sub