In this Article
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 EasyStop 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!