Copy Single Line data from Text file to Excel
In the first example, the macro will read a "single line data" from the text file. The FileSystemObject, which I have mentioned in the beginning of the post, is used to interact with local files and folders. However to read the contents of the file, you’ll also have to use the TextStream class in VBA.
TextStream class provides two useful methods namely ReadLine and ReadAll. See the below image.
To read a single line (the first line for example), I’ll use the ReadLine method. For example,
Option Explicit Dim objFso As New FileSystemObject ' Create FSO object. Dim objTS As TextStream ' For TextStream object. Dim sFolder As String Private Sub Worksheet_Activate() readFile End Sub Sub readFile() sFolder = "d:\fixtures" Set objTS = objFso.OpenTextFile(sFolder & "/my-test-file.txt") ' Open the file. Do While Not objTS.AtEndOfLine ' Read data till the end of the line. Dim sData As String sData = objTS.ReadLine ' Copy data in a string variable. Dim arrData arrData = Split(sData, " ") ' Split the string and populate the array. Dim iColCnt For iColCnt = 0 To UBound(arrData) ' Loop through the array and copy each word in a cell. Cells(1, iColCnt + 1) = arrData(iColCnt) Next iColCnt Loop objTS.Close 'Close the file. End Sub
Here’s what I am doing. I am using the "OpenTextFile()" method to open the file and then initialized a "Textstream" object (objTS). Using the object’s properties, I am reading the contents of the file (the first line only) and populating an array with the data.
Next, I am running a "for loop" to read each word (commas, full stops etc.) in the array and write it each cell in my Excel worksheet.
👉 Open a Word document from Excel and copy entire Excel worksheet to the Word doc with the click of a button using VBA.
👉 If want to write all the Excel data back to a text file, check out this article on how to write data to a text file using a VBA macro.
Copy Multiple Lines of data from Text file to Excel
To copy multiple lines of data or all the contents in the text file to my Excel worksheet, I simply have to make few changes in the above code.
Option Explicit Dim objFso As New FileSystemObject ' Create FSO object. Dim objTS As TextStream ' For TextStream object. Dim sFolder As String Private Sub Worksheet_Activate() ReadFile End Sub Sub ReadFile() sFolder = "d:\fixtures" Set objTS = objFso.OpenTextFile(sFolder & "/my-test-file.txt") ' Open the file. Do While Not objTS.AtEndOfLine Dim sData As String sData = objTS.ReadAll ' Copy data in a string variable. Dim arrData arrData = Split(sData, " ") ' Split the string and populate the array. Dim iColCnt, iRowCnt, iCellCnt iCellCnt = 0 iRowCnt = 1 ' Loop through the array and copy each word in a cell. For iColCnt = 0 To UBound(arrData) If InStr(arrData(iColCnt), vbNewLine) > 0 Then Cells(iRowCnt, iCellCnt + 1) = Mid(arrData(iColCnt), 1, InStr(arrData(iColCnt), vbNewLine) - 1) iRowCnt = iRowCnt + 1 iCellCnt = 1 Cells(iRowCnt, iCellCnt) = Mid(arrData(iColCnt), InStr(arrData(iColCnt), vbNewLine) + 1, Len(arrData(iColCnt))) Else Cells(iRowCnt, iCellCnt + 1) = arrData(iColCnt) iCellCnt = iCellCnt + 1 End If Next iColCnt Loop objTS.Close 'Close the file. End Sub
There’s one major difference in this macro and first macro above, is the use of ReadAll method of the "TextStream" class.
Dim sData As String
sData = objTS.ReadAll
Here again I am splitting the contents of the file using the Split() method and storing data in an "array".
Inside the "for loop" I have set a condition to check if the data has a "new line". If yes, it will write the data in a new row in the Excel work sheet.
👉 Also, see how to create, write and read text file in Excel using VBA FileSystemObject
You can make changes according to the type of content in your .txt file. I have tested the above example using data structure like,
Or
Well, that’s it. Hope you’ll find the examples useful. Thanks for reading. ☺