Copy Data from text File to Excel using VBA

← PrevNext →

You can use the FileSystemObject in VBA to read and write data to an external text 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 show 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. Here’s the 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.

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 from 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.

Related: How to Create, Write and Read text file in Excel using VBA FileSystemObject

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.

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

Or

Copy multiple lines from a Text file to Excel using VBA

Well, that’s it. Hope you’ll find the examples useful. Thanks for reading.

← PreviousNext →


Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.
Delivered by FeedBurner