Use the FileSystemObject TextStream to create a text file with VBA CreateTextFile. The TextStream is a class of the Microsoft Scripting Runtime object library and the CreateTextFile method, as you might have guessed, is a handy way to quickly create text files. The combination of TextStream and CreateTextFile make creating text files a breeze.

In case you haven’t already done so, read our tutorial on writing to a text file with VBA Print statement for an alternate way to create text files in VBA. Also check out our introduction to the VBA FSO tutorial for background info on the scripting object used for creating text files in this tutorial.

In this tutorial we’ll show you how to create a text file using the FileSystemObject (FSO) and the methods of the CreateTextFile object. We’ll start with the basic setup of a TextStream and explain the various methods for writing text content to it. Next, we’ll show you a more advanced example where you pass an input range as a parameter to a procedure which then creates a text file containing all the text from your input. You’ll be able to separate the rows and columns by a delimiter of your choice in the text file. Finally, we’ll briefly discuss some powerful application ideas and when not to use the FSO CreateTextFile object for creating files.

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


VBA CreateTextFile Basic Setup

Start by opening the Visual Basic Editor by pressing Alt+F11. Then, go to Tools > References… and set a reference to the Microsoft Scripting Runtime object library.

VBA Microsoft Scripting Runtime Reference
VBA Microsoft Scripting Runtime Reference

Next, paste the code below into a standard code module:

Sub CreateTextFileBasic()
    '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) Write two example lines of text 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 class.
    Set tsTxtFile = fso.CreateTextFile("C:\Test\test.txt", True)
    
    ' (3) Write two example lines of text to the TextStream object.
    tsTxtFile.WriteLine "This is the first line"
    tsTxtFile.WriteLine "This is the second line"
    
    ' (4) Close the textstream object
    tsTxtFile.Close

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 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 CreateTextFile Auto List Members
VBA CreateTextFile Auto List Members

The members of this object, listed in the picture above, are not all write methods. This object can also be used for reading text files. In this tutorial, however, we’ll focus solely on the write methods of the CreateTextFile object but look forward to our future tutorial devoted entirely to the read methods of this object.

In the basic setup procedure above, we first set up the fso variable to hold an instance of the FileSystemObject. Next, we set up the tsTxtFile</mod> variable to hold the information of the text file we're creating by means of the CreateTextFile method of the FileSystemObject.

The CreateTextFile method takes three parameters, the first one mandatory and the next two optional.

The first parameter, "C:\Test\test.txt", is the full file path and name of the text file you’re creating.

The second parameter, True, is a Boolean type variable which specifies whether you want to overwrite any existing file by the same name in the specified file path. If you set this parameter to False and run the procedure and a file by the same name already exists, an error message will appear at run-time:

VBA File already exists run-time error 58
VBA File already exists run-time error 58

The third parameter is a Boolean type variable which specifies whether you want to encode the text using the ANSI (American National Standards Institute) code page or Unicode (an international encoding standard). The ANSI code page is the “native” or standard code page on a computer system whereas Unicode is typically used when you need to include “foreign” or other special characters in your text file. The default value of this optional parameter is False (ANSI).

Now, let’s take a closer look at the very useful write methods of the TextStream object one by one!


TextStream Write methods

The write methods of the TextStream object are shown in the table below.

Method Description
.Write The method takes as parameter the string to be written to the TextStream. The input string is appended to any existing content in the TextStream on the same line as the existing content.
.WriteBlankLines The method takes one parameter, the number of blank lines to be written to the TextStream. This is the TextStream equivalent of the vbNewLine constant used in standard VBA syntax for carriage returns in strings.
.WriteLine The method takes as parameter the string to be written to the TextStream and adds a carriage return to it. In other words, the input string is appended as a separate line to the TextStream.

Hopefully, these methods seem quite intuitive to you. You access them by chaining onto (adding a dot behind) the tsTxtFile</mod> variable in our CreateTextFileBasic procedure.

It’s now time to present a more advanced example where we create a text file based on values from an input range! This is actually a really useful example, so stick with me.


Create text file from range of values

First, insert the following example data into cell “A1” of an empty sheet:

Index Value
1 A
2 B
3 C

Next, paste the code below into a standard code module:

Sub CreateTextFileFromRange(rngInput As Range, strFileName As String, strDelim As String)

    'Must add reference to Tools > References > Microsoft Scripting Runtime
    Dim rngCol As Range, rngRow As Range
    Dim fso As Scripting.FileSystemObject
    Dim strRow As String
    Dim tsTxtFile As Scripting.TextStream
    
    Set fso = New Scripting.FileSystemObject
    Set tsTxtFile = fso.CreateTextFile(strFileName, True)
    
    ' Write each line of text in the range to the text file
    For Each rngRow In rngInput.Rows
        strRow = vbNullString
        For Each rngCol In rngRow.Columns
            strRow = strRow & strDelim & rngCol.Text
        Next rngCol
        
        ' write concatenated row content to separate new line
        tsTxtFile.WriteLine Right(strRow, Len(strRow) - 1)
    Next rngRow
    
    ' Close textstream
    tsTxtFile.Close
    
    ' Open text file in Notepad
    Call Shell("notepad.exe """ & strFileName & """", vbNormalFocus)

End Sub

You invoke the procedure above like this:

Sub ConvertRangetoFile()
Call CreateTextFileFromRange(ThisWorkBook.Sheets("Test").Range("A1:B4"), "C:\Test\test.txt", Chr(9))
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 first parameter of the procedure, ThisWorkBook.Sheets("Test").Range("A1:B4"), is the input range from which you want to create your text file. Remember to change the sheet name to the name of the sheet in your workbook to avoid errors! The text in the range you specify will be saved to a text file, like this:

Use VBA to convert range to text file
Use VBA to convert range to text file

The second parameter, "C:\Test\test.txt", is the path and name of the text file you want to create. Make sure you specify a valid path before the file name. If the folder you specify in the path does not exist, 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

To avoid this error, you can use the VBA Dir function and pair it with VBA MkDir to make your folder if it doesn’t already exist.

The third parameter, Chr(9), is the delimiter you use for separating the values of the cells in each row of the specified input range. In this example we’ve used the VBA Chr function to insert the horizontal tab character, which is the ASCII (American Standard Code for Information Interchange) value of character code 9. Another character you might want to use is the comma, Chr(44), for making CSV files.

Next, the CreateTextFileFromRange procedure loops through all the rows of the input range. For each cell in each column of the current row, it then concatenates the text content of the cell with the delimiter specified in the second parameter (the string variable, strRow) of the procedure. The first character of strRow is then removed before the concatenated text content of the row is appended to the TextStream on a separate line with the .WriteLine method.

Note, multiline cells will be printed on the same line by the procedure! If you wish to have these printed on separate lines, you need to incorporate a check for the linefeed character, vbLf into the procedure. An easy way to detect multiline cells is shown below:

If InStr(1, rngCol.Value, vbLf) > 0 Then Debug.Print "Multiline: " & rngCol.Text

After looping through all the rows, we close the TextStream with the .Close command. This is not strictly necessary as your text file has already been created at this point and Excel has built-in deterministic garbage collection (automatic destruction of objects when they go out of VBA scope). However, it is considered a “best practice” to do so, especially if you’re writing more sophisticated procedures where you need the memory used by the object to be released as quickly as possible.

Finally, as a bonus, we’ve included a VBA Shell command which displays your newly created text file in the Notepad text editor. You can replace the reference with that of another text editor if you wish, or you can simply delete this line of code if it doesn’t suit your needs at all.


CreateTextFile Application Ideas

Separate text files are created for a multitude of reasons and some of the most important ones are listed below:

  • Log files. Let’s say you want to distribute an application to several of your coworkers. You could then create a log files with individual user information on a shared network drive, rather than having to examine each individual instance of the application each time an error occurred. I’ve also used log files when exporting outlook emails to a folder for archival. The log file lists files that could not be exported for whatever reason.
  • Collection of data/data extraction. The CreateTextFile object is very powerful in combination with other methods of the FSO, which allow you to easily iterate through folders and files and retrieve any information you require. This is particularly true when you need to construct a text file to be imported into another computer system, like a financial management system.
  • Data backup. In case you want to do personal backups of sensitive data in your files and you want to take up as little disk space as possible, generating separate data text files could be the solution for you.

One word of caution, though. As we noted in our introduction to the VBA FSO tutorial, if you’re writing a program meant to be used by people in different countries, you should NOT use the FSO for writing to binary files! This is because the user’s default ANSI code page may differ from yours. Some countries, such as Japan, uses a double-byte character set, whereas most Western countries, including the US, don’t. Unless the character sets match, the bytes you’re writing to the file will be interpreted incorrectly!


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.