VBA – Read Text File Line by Line
This tutorial will demonstrate how to read content from text files line by line with VBA.
We can either read the text file as an entire file, or line by line.
Read Text File Line by Line
Text in a text file is usually made up of several lines, separated by delimiters. These can be a comma (“,”), a comma with space (“, “), a semicolon (“;”), a semicolon with space (“; “), a space (“ “), a tab (vbTab) or in rare cases any other character such as a tilde (~). The lines are normally separated by a line break (vbCRLF).
The easiest way to read a text file line by line into our Worksheet in VBA is to select the first cell where we want the text to be put, and then run the following code:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub ReadFile() Dim strFile As String, strLine As String strFile = "C:\Test\TestFile.txt" Open strFile For Input As #1 Do Until EOF(1) Line Input #1, strLine ActiveCell = strLine ActiveCell.Offset(1, 0).Select Loop Close #1 End Sub |
This will put each line of the text file into a single cell in Excel.
We can also read a text file in VBA by using the FileSystemObject. In the code below we have used late binding with the File System Object. You can also create a reference to it in your VBA project. See here for more information.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub ReadTextFile() Dim strLine As String Dim FSO as Object Dim TSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") Set TSO = FSO.OpenTextFile("C:\Test\TestFile.txt") Do While Not TSO.AtEndOfStream strLine = TSO.ReadLine ActiveCell = strLine ActiveCell.Offset(1, 0).Select Loop TSO.Close Set TSO = Nothing Set FSO = Nothing End Sub |
We can create a slightly more complicated loop if we want to separate the lines into cells by their delimiters. Here we have used early binding in the code and declared the File System Object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
Sub ReadTextFileWithSeparators() Dim StrLine As String Dim FSO As New FileSystemObject Dim TSO As Object Dim StrLineElements As Variant Dim Index As Long Dim i As Long Dim Delimiter As String Set FSO = CreateObject("Scripting.FileSystemObject") Set TSO = FSO.OpenTextFile("C:\Test\TestFile.txt") Delimiter = "," Index = 1 Do While TSO.AtEndOfStream = False StrLine = TSO.ReadLine StrLineElements = Split(StrLine, Delimiter) For i = LBound(StrLineElements) To UBound(StrLineElements) Cells(Index, i + 1).Value = StrLineElements(i) Next i Index = Index + 1 Loop TSO.Close Set TSO = Nothing Set FSO = Nothing End Sub |
This will result in the lines being separated into individual cells in Excel as per the graphic below.
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!
