Read and Write to a Text File with VBA OpenTextFile

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 OpenTextFile object here.

• 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 OpenTextFile method. Next, we’ll present three examples of reading, appending and writing to a text file. Finally, we’ll briefly elaborate on some of the powerful application ideas related to the topic which we’ve already discussed in previous tutorials.

Now, let’s get started with the FSO setup!

FileSystemObject 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.

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, along with our Big Book of Excel VBA Macros, to your email address below.

The OpenTextFileRead procedure above will print all the lines of a text file to the immediate window in the Visual Basic Editor. Let’s explain in more detail how that works.

The TextStream object is used for creating the text file and we use the tsTxtFile variable to hold an instance of this object. Notice that we use early binding by means of the New operator, which allows us to view the methods and properties of the TextStream object at design time with the “Auto List Members” feature of the Visual Basic Editor.

VBA OpenTextFile Auto List Members

In the procedure above, we first set up the fso variable to hold an instance of the FileSystemObject. Next, we set up the tsTxtFile variable to hold the information of the text file we’re working with by means of the OpenTextFile method of the FileSystemObject.

The OpenTextFile method takes four parameters, the first one mandatory and the next three optional.

VBA OpenTextFile parameters

The first parameter, "C:\test\test.txt", is the full file path and name of the text file you’re working with and it should be self-evident why this parameter is mandatory. It’s hard to open a file if you don’t tell it which file to open!

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:

The second parameter, IOMode, or Input-Output mode, is a class of the MSR object library and has three members, or “options”. You must choose which IOMode you want to use while processing the text file as you cannot both read and append or write to it simultaneously.

The third parameter, Create is only relevant if you set the second parameter, IOMode, to something other than ForReading. If you set this parameter to False and specify an invalid folder path in the first parameter, you will see the following error message at run-time:

VBA Path does not exist run-time error 76

The fourth parameter, Format is a class of the MSR object library with four options. These four options are crucial to know if you’re working with text files in non-ASCII formats. Curiously, both the TristateMixed and TristateUseDefault constant have the value -2 and are therefore interchangeable! A possible explanation for this is that the one of these is a legacy option carried over from another variant of the VB language, such as VBScript, where the MSR object library and the FileSystemObject is used for similar purposes as in VBA. Selecting either of these two options means the OpenTextFile method will try to determine for you whether the file in question is an ASCII or Unicode file. This is extremely useful if you want to process a batch of text files from various sources and you’re unsure how they are encoded.

Conversely, if you choose the TristateFalse or TristateTrue option, you force the method to interpret the input text as ASCII or Unicode, respectively.

Finally, after looping all the lines of text, we close the TextStream with the .close command.

Now, let’s take a closer look at how you append content to a text file using the OpenTextFile method!

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 "[1] Appended at the end of the TextStream WITHOUT a carriage return"
        .WriteLine "[2] 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 OpenTextFileRead procedure, except we’ve changed the second parameter of the VBA OpenTextFile method to ForAppending. You’ll also notice that we’re appending with three different methods, so let’s explain these in more detail:

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 OpenTextFileAppend procedure will still work.

After appending to the TextStream, we close it with the .Close command. As we mentioned in our VBA CreateTextFile tutorial, this isn’t strictly necessary but if you’re working with very large files you probably want the memory used by the TextStream object to be released as quickly as possible.

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 OpenTextFile method in the OpenTextFileAppend procedure to

Set tsTxtFile = fso.OpenTextFile("C:\test\test.txt", ForWriting, False, TristateMixed)

Notice the ForWriting keyword. The procedure will now work exactly the same way as described in the appending section, except that it overwrites any existing content in the specified input text file! In other word, after you run the procedure the only content in the file will be what you just wrote to it.

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.
• Data backup. If you’re creating personal backups in text files, you can now shrink the disk space required to do so even further by using incremental backups rather than full backups. This is pretty advanced stuff, but here’s the general idea. Let’s say you’ve created a master file (“full backup”) of sensitive information gathered from various sources. Every time crucial changes are made to this information, you could then append the changes to a separate JSON (Javascript Object Notation) text file containing key-value pairs specifying where the changes were made and what they are. This would allow you to roll back to any “configuration” or former state of the initial information without storing bloated and redundant backup copies on your disk.

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.