Reading a text file in VBA (reading, parsing and importing)

In this tutorial, we will show how to read content from text files and place it into worksheets with VBA.

Reading content from a text file into a worksheet

The simplest way to read the contents of a text file is to copy it into a worksheet cell.

Sub FSOPasteTextFileContent() 
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileToRead = FSO.OpenTextFile("C:TestTestFile.txt", ForReading) 'add here the path of your text file
    
    TextString = FileToRead.ReadAll
    
    FileToRead.Close
    
    ThisWorkbook.Sheets(1).Range("A1").Value = TextString 'you can specify the worksheet and cell where to paste the text file’s content

End Sub

The above code uses the FileSystemObject object. To use it, you will need to specify a reference to the VB scripting library. See here for more information.

Without using FileSystemObject, you can insert the contents of your text file with the code below. If your text file contains a line separator, it will be inserted line by line.

Sub PasteTextFileContent () 
    Dim wbExcel As Workbook, wbText As Workbook
    Dim wsExcel As Worksheet
    Set wbExcel = ThisWorkbook 'specify here which Excel file the text file’s content is to be pasted into
    Set wsExcel = wbExcel.Sheets(1) 'specify here which worksheet to use
    Set wbText = Workbooks.Open("C:TestTestFile.txt") 'add here the path of your text file

    wbText.Sheets(1).Cells.Copy wsExcel.Cells

    wbText.Close SaveChanges:=False

End Sub

Reading the contents of a text file line by line, column by column

Your text file may have multiple lines and multiple items listed in the lines, separated by commas, semicolons, tabs, spaces, etc. To read and insert the contents of your text file correctly, you may need this code below:

Sub PasteTextFileContentWithSeparators() 
    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:TestTestFile.txt")

    Delimiter=", " 'the delimiter that is used in your text file
    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) 'this code will start pasting the text file’s content from the active worksheet’s A1 (Cell(1,1)) cell
       Next i
       Index = Index + 1
    Loop

TSO.Close

End Sub

The delimiter used in your text file can be a comma (“,”), a space (“, “), a semicolon (“;”), a semicolon with a space (“; “), a space (” “), a tab (change then Delimiter = vbTab), or in rare cases any other character.

Reading text files in arrays

If you need to read the contents of your text file into an array and insert it line by line, column by column into your worksheet, you will need this code below:

Sub ReadDelimitedTextFileIntoArray()
    Dim Delimiter As String
    Dim TextFile As Integer
    Dim FilePath As String
    Dim FileContent As String
    Dim LineArray() As String
    Dim DataArray() As String
    Dim TempArray() As String
    Dim rw As Long, col As Long

    Delimiter = vbTab 'the delimiter that is used in your text file
    FilePath = "C:TestTestFileTab.txt"
    rw = 1 
    
    TextFile = FreeFile
    Open FilePath For Input As TextFile 
    FileContent = Input(LOF(TextFile), TextFile)
    Close TextFile

    LineArray() = Split(FileContent, vbNewLine) 'change vbNewLine to vbCrLf or vbLf depending on the line separator that is used in your text file
    For x = LBound(LineArray) To UBound(LineArray)
        If Len(Trim(LineArray(x))) <> 0 Then
           TempArray = Split(LineArray(x), Delimiter)
           col = UBound(TempArray)
 	   ReDim Preserve DataArray(col, rw)
           For y = LBound(TempArray) To UBound(TempArray)
 	       DataArray(y, rw) = TempArray(y)
 	       Cells(x + 1, y + 1).Value = DataArray(y, rw)  'this code will start pasting the text file’s content from the active worksheet’s A1 (Cell(1,1)) cell
           Next y
        End If 
        rw = rw + 1
     Next x

End Sub

The line delimiters in your text file can be a combination of carriage returns and line breaks (Chr(13)+Chr(10)) or line breaks (Chr(10)). Use vbCrLf or vbLf respectively. If unsure, use vbNewLine to specify the line separator.

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!