You can use the FileSystemObject TextStream to open a text file in VBA by pairing it with the VBA OpenTextFile method. The VBA OpenTextFile method is a simple way to read, write, and append to text files.
In case you haven’t already done so, check out our VBA FSO introduction tutorial for background info on the scripting object used for opening text files in this tutorial.
In this tutorial we’ll show you how to read a text file using the FileSystemObject (FSO) and the methods of the VBA OpenTextFile method. We’ll start with the basic setup of a TextStream and explain the various methods for reading text content from it. Since we’ve already dealt extensively with how to create text files through VBA in our VBA CreateTextFile tutorial, we’ll mainly focus on the read, append and write methods of the of the
- FileSystemObject Setup
- The TextStream class
- Reading a text file with VBA OpenTextFile
- Appending to text file with VBA OpenTextFile
- Writing to text file with VBA OpenTextFile
- OpenTextFile Application Ideas
First, we’ll show you the basic setup of the FSO and then we’ll give a quick rundown of the TextStream class, as it contains the methods and properties you’ll be using when you’re working with text files through the
Now, let’s get started with the FSO setup!
Start by opening the Visual Basic Editor by pressing Alt+F11 and then go to Tools > References… and set a reference to the Microsoft Scripting Runtime (MSR) object library.
VBA Microsoft Scripting Runtime Reference
You’re now set up to start using the various scripting methods, such as reading and writing to text files. Before we discuss these methods, let’s take a closer look at the TextStream class.
The TextStream class
I encourage you to return to this table once you’ve finished reading the rest of this tutorial. This table describes a bunch of the methods of the VBA TextStream class that you may find useful when working with text files.
|AtEndOfLine||Property||Read-only. Checks if the current position is the end of a line.|
|AtEndOfStream||Property||Read-only. Checks if the current position is the end of the TextStream.|
|Close||Method||Closes the TextStream object.|
|Column||Property||Read-only. Returns the column number of the character currently being read in the TextStream.|
|Line||Property||Read-only. Returns the row number of the character currently being read in the TextStream.|
|Read||Method||Reads a specified number of characters into a string.|
|ReadAll||Method||Reads all the content of the input text file into a string. This should be used with caution if you're working with very large files. Example usage:
|ReadLine||Method||Reads the content of a specified line of the TextStream into a string. Example usage:
|Skip||Method||Ignore the specified number of characters. Typically part of a conditional statement, such as
|SkipLine||Method||Same principle as the Skip method but ignores the entire line of characters instead and takes no parameter.|
The class contains three more methods, namely Write, WriteBlankLines and WriteLine, but we’ll deal with them in the append example later on.
Reading a text file with VBA OpenTextFile
We’re going to show you how to read a text file with the VBA OpenTextFile method, but to become a true file I/O expert, you should look at our comprehensive VBA File I/O Cheat Sheet filled with 50+ tips for working with files and over 30 file input/output macro examples.
Paste the code below into a standard code module:
Sub OpenTextFileRead() ' Must add reference to Tools > References > Microsoft Scripting Runtime ' (1) Open an instance of the FileSystemObject. ' (2) Open an instance of the FileSystemObject TextStream. ' (3) Read each line of text in the TextStream object. ' (4) Close the TextStream object. Dim fso As Scripting.FileSystemObject Dim tsTxtFile As Scripting.TextStream ' (1) Open an instance of the FileSystemObject. Set fso = New Scripting.FileSystemObject ' (2) Open an instance of the FileSystemObject TextStream. Set tsTxtFile = fso.OpenTextFile("C:\test\test.txt", ForReading, False, TristateMixed) With tsTxtFile ' (3) Read each line of text in the TextStream object. Do Until .AtEndOfStream ' ************************************ ' insert your code here ' ************************************ Debug.Print .Line & ": " & .ReadLine ' remove in production Loop ' (4) Close the TextStream object. .Close End With End Sub
Make powerful macros with our free VBA Developer Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below.
This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below.
The TextStream object is used for creating the text file and we use the
VBA OpenTextFile Auto List Members
In the procedure above, we first set up the
VBA OpenTextFile parameters
The first parameter,
The next three parameters do require more explaining, especially if you’re working with non-ASCII text files. (ASCII stands for American Standard Code for Information Interchange and is the most common format for text files in computers and on the Internet). These three optional parameters are described in the table below:
|Parameter||Option||Option data type||Value||Default value||Description|
|Appends lines of text to the TextStream.|
|ForReading||Long||1||Reads lines of text from the TextStream.|
|ForWriting||Long||2||Writes lines of text to the TextStream.|
|Create||True/False||Boolean||True/False||False||Creates a text file in the specified path location, if no file by the specified name already exists in it.|
|Opens the file as an ASCII file.|
|TristateMixed||Long||-2||Opens the file either as an ASCII file or a Unicode file.|
|TristateTrue||Long||-1||Opens the file as a Unicode file.|
|TristateUseDefault||Long||-2||Same as TristateMixed.|
The second parameter,
The third parameter,
VBA Path does not exist run-time error 76
The fourth parameter,
Conversely, if you choose the
Finally, after looping all the lines of text, we close the TextStream with the
Now, let’s take a closer look at how you append content to a text file using the
Appending to text file with VBA OpenTextFile
Paste the code below into a standard code module:
Sub OpenTextFileAppend() ' Must add reference to Tools > References > Microsoft Scripting Runtime ' (1) Open an instance of the FileSystemObject. ' (2) Open an instance of the FileSystemObject TextStream. ' (3) Append lines to the TextStream object. ' (4) Close the TextStream object. Dim fso As Scripting.FileSystemObject Dim tsTxtFile As Scripting.TextStream ' (1) Open an instance of the FileSystemObject. Set fso = New Scripting.FileSystemObject ' (2) Open an instance of the FileSystemObject TextStream. Set tsTxtFile = fso.OpenTextFile("C:\test\test.txt", ForAppending, False, TristateMixed) With tsTxtFile ' (3) Append lines to the TextStream object. .WriteBlankLines 1 .Write " Appended at the end of the TextStream WITHOUT a carriage return" .WriteLine " Appended as a separate line WITH a carriage return" ' (4) Close the TextStream object. .Close End With End Sub
The setup is very similar to the
|.Write||Adds text to the end of the TextStream without adding a carriage return.|
|.WriteBlankLines||Adds a carriage return to the end of the TextStream.|
|.WriteLine||Adds a carriage return and then the specified text to the end of the TextStream. A shorthand for writing
Note, it doesn’t matter whether or not the specified input text file actually contains any characters before you start appending to it. Even if it’s empty, the
After appending to the TextStream, we close it with the
Writing to text file with VBA OpenTextFile
We already described how to write to a text file with the VBA Print statement, but you may prefer to create files using FSO, instead.
To write to a text file with VBA OpenTextFile FSO, start by changing the parameters of the
Set tsTxtFile = fso.OpenTextFile("C:\test\test.txt", ForWriting, False, TristateMixed)
OpenTextFile Application Ideas
VBA OpenTextFile is extremely powerful since it allows you to both create, read, write and append to text files. We’ve focused mainly on the three latter operations in this tutorial, since the former is covered in our VBA CreateTextFile tutorial. Rather than repeating our application ideas from that tutorial, let’s instead append to them (pun intended):
- Log files. When you’re creating log files you usually want to append to the end of them, rather than overwriting them or creating new separate files.
- Collection of data/data extraction. By modifying the procedures presented in this tutorial you can easily update or overwrite any existing data you’ve collected in text files.
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.
Oh, and if you have a question, post it in our VBA Q&A community.
The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That's why I'm giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.