Copy Data from text File to Excel using VBA

You can use the FileSystemObject in VBA to read and write data to txt file from Excel. The FileSystemObject provides all necessary methods and properties that would help VBA developers to work with many different file formats. In this post, I am sharing two different examples that shows how to copy or extract data from a text file (.txt file) to an Excel file using VBA.

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.

Copy data from Text file in Excel using TextStream class

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"
    
    ' Open the file.
    Set objTS = objFso.OpenTextFile(sFolder & "/my-test-file.txt")
    
    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

        ' Loop through the array and copy each word in a cell.
        For iColCnt = 0 To UBound(arrData)
            Cells(1, iColCnt + 1) = arrData(iColCnt)
        Next iColCnt
    Loop

    objTS.Close        'Close the file.
End Sub

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.

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,

Extract data from Text file to Excel using VBA Macro

Copy multiple lines from a Text file to Excel using VBA

← PreviousNext →