Excel 2010 Macro Read Text File
This sample takes a different approach to reading a text file. The macro loads the whole text file into memory in a single string variable instead of reading one record at a time. The macro then breaks down the string into individual records. The advantage to this method is that the file on disk is accessed only one time. All processing thereafter occurs in memory and is very fast:
Sub ReadTxtLines()
‘No need to install Scripting Runtime library since we used late binding
Dim sht As Worksheet
Dim fso As Object
Dim fil As Object
Dim txt As Object
Dim strtxt As String
Dim tmpLoc As Long
‘Working on active sheet
Set sht = ActiveSheet
‘Clear data in the sheet
sht.UsedRange.ClearContents
‘File system object that we need to manage files
Set fso = CreateObject(“Scripting.FileSystemObject”)
‘File that we like to open and read
Set fil = fso.GetFile(“c:\test.txt”)
‘Opening file as a TextStream
Set txt = fil.OpenAsTextStream(1)
‘Reading file include into a string variable at once
strtxt = txt.ReadAll
‘Close textstream and free the file. We don’t need it anymore.
txt.Close
‘Find the first placement of new line char
tmpLoc = InStr(1, strtxt, vbCrLf)
‘Loop until no more new line
Do Until tmpLoc = 0
‘Use A column and next empty cell to write the text file line
sht.Cells(sht.Rows.Count, 1).End(xlUp).Offset(1).Value = _
Left(strtxt, tmpLoc - 1)
‘Remove the parsed line from the variable that we stored file include
strtxt = Right(strtxt, Len(strtxt) - tmpLoc - 1)
‘Find the next placement of new line char
tmpLoc = InStr(1, strtxt, vbCrLf)
Loop
‘Last line that has data but no new line char
sht.Cells(sht.Rows.Count, 1).End(xlUp).Offset(1).Value = strtxt
‘It will be already released by the ending of this procedure but
‘ as a good habit, set the object as nothing.
Set fso = Nothing
End Sub