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.

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

Name Type Description
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: str = tsTxtFile.ReadAll, where str is a String type variable and the tsTxtFile variable holds the TextStream object.
ReadLine Method Reads the content of a specified line of the TextStream into a string. Example usage: str = tsTxtFile.ReadLine, where str is a String type variable and the tsTxtFile variable holds the TextStream object.
Skip Method Ignore the specified number of characters. Typically part of a conditional statement, such as If .Line = 1 Then .Skip 10. "If the line is the first of the TextStream, then skip 10 characters before performing some action".
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

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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
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
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:

Parameter Option Option data type Value Default value Description



IOMode
ForAppending Long 8


ForReading
(1)
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.




Format
TristateFalse Long 0



TristateFalse
(0)
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, 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
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

Write better macros in half the time
I see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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:

Method Description
.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 .WriteBlankLines 1 and .Write [text] in two separate lines of code.

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.

Comments

If you haven’t already done so, please subscribe to my free wellsrPRO VBA Training Program. What are you waiting for? You’ll love the great VBA content I send your way!

Share this article on Google+, Twitter and Facebook, then leave a comment below and let’s have a discussion.

This article was written by Michael H. Sorensen on behalf of The VBA Tutorials Blog.

About The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.