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:
1 |
Set FileToRead = FSO.OpenTextFile("C:\Test\TestFile.txt", ForReading) |
and then paste its content to the current worksheet’s first cell, e.g.:
1 2 3 4 5 6 7 8 9 10 |
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.
1 2 3 4 5 6 7 8 9 |
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.
1 2 3 4 5 6 7 8 9 |
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!