VBA Open Text File with OpenTextFile

This tutorial will demonstrate how to use the OpenTextFile method of the FileSystemObject.

Open a Text File

This lesson uses the FileSystemObject. In order to use it, you will need to set a reference to the VB script run-time library. See here for more information.

You can open an existing text file for reading:

Set FileToRead = FSO.OpenTextFile("C:\Test\TestFile.txt", ForReading)

and then paste its content to the current worksheet’s first cell, e.g.:

Sub FSOReadFromTextFile() 
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileToRead = FSO.OpenTextFile("C:\Test\TestFile.txt", ForReading)

    TextString = FileToRead.ReadAll
    FileToRead.Close
    ThisWorkbook.Sheets(1).Range("A1").Value = TextString

End Sub

For more options with ForReading mode see here.

In ForWriting mode you can replace an existing file’s content with new data but you can’t read from the file.

Sub FSOWriteToTextFile() 
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileToWrite = FSO.OpenTextFile("C:\Test\TestFile.txt", ForWriting)

    FileToWrite.Write "test line”
    FileToWrite.Close

End Sub

For more options with ForWriting mode see here.

In ForAppending mode you can write to the end of the file. You can’t read from this file.

Sub FSOAppendToTextFile() 
    Dim FSO As New FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set FileToAppend = FSO.OpenTextFile("C:\Test\TestFile.txt", ForAppending)

    FileToAppend.Write "appended content"
    FileToAppend.Close

End Sub

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!