VBA Write to Text File with Print Statement

Introduction - VBA Write Text File

Use VBA to write to a text file by using the Print Statement or the Write Statement. This tutorial will show you how to use VBA to write to a file using the “Open For Output As” commands.

Creating text files is easy with Excel VBA, but how do you know which method to use? To become a true file input/output 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 manipulation macro examples.

Example - VBA Write Text File

Sub WriteToTextFile()
Dim iLastRow As Long
Dim iLastCol As Long

iLastRow = Range("A" & Rows.Count).End(xlUp).Row
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Open "C:\Users\Ryan\Documents\wellsr\FundPrices.txt" For Output As #1
        For i = 1 To iLastRow
            For j = 1 To iLastCol
                If j <> iLastCol Then 'keep writing to same line
                    Print #1, Cells(i, j),
                Else 'end the line
                    Print #1, Cells(i, j)
                End If
            Next j
        Next i
        'MsgBox "Failed to transfer " & iFail & " file(s).", iFail & " Transfer(s) Failed"
    Close #1
    'comment the shell command out if you don't want to open the file when the macro ends
    Shell "notepad.exe ""C:\Users\Ryan\Documents\wellsr\FundPrices.txt", vbNormalFocus
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

Tutorial - VBA Write Text File

Let’s pretend you have a spreadsheet with the following mutual fund prices:

You want to write this data to a text file, called FundPrices.txt. The example above does just that.

I’ll explain how it works.

VBA Open File For Output

Notice the Open statement near the top of the macro. The open statement tells VBA you’re ready for file I/O (input/output).

In the most basic terms, you need to pass the Open statement a file name with the directory path, a mode telling VBA what you want to do with the file, and an integer which tells VBA how you want to refer to the file.

For writing to a new file, the format looks like this

Open FilePath For Output As #FileNumber

By declaring the mode as Output, VBA will overwrite any file that exists with that name already. There are other options you can use with the Open statement that would prevent this, but we won’t get into them here.

If you want to append text to the end of an existing file, you would change the keyword Output to Append

The FileNumber is a unique number you give your file. In the example above, I gave it the number 1. Remember this number! This is how you’ll tell VBA which file to write to.

VBA Print vs Write

There are two ways to write content to a line in a file. You can use the Print command or the Write command. The Print command prints your strings and numbers exactly as you would expect them to appear in Excel. For example, running the above example with the Print statement produces output like this:

However, if you use the Write statement, VBA tries to automatically delimit your list with commas between values, quotes around strings and # signs around dates. Here’s what the output would look like if you replaced the word Print with Write:

Whichever method you choose, the syntax is the same. Just remember, if you don’t want your output to have quotes, use Print. Print is more reader-friendly, and Write is more programmer friendly. Let’s spend a few minutes talking about the syntax:

VBA Writing to a Line

Whether you choose to use the Print statement or the Write statement, your approach is the same. The format of each statement looks like this:

Print #FileNumber, WhatYouWantToPrint

or

Write #FileNumber, WhatYouWantToPrint

The #FileNumber is the same integer you used when you opened your file for Output. In our example, that number was #1.

After you put the number, you put a comma followed by whatever you want to print to your file. It can be a string, a variable or a cell in Excel. Here’s another example:

Sub SimpleWriteToTextFile()
    Open "C:\Users\Ryan\Documents\wellsr\DemoFile.txt" For Output As #1
        Print #1, "This is cell B2: " & Range("B2")
    Close #1
End Sub

You can tell VBA whether or not you want to continue writing information to the same line or if you want to start writing content to a new line. The only difference in syntax is the use of a comma , at the end of your Print or Write statement.

Adding the , tells VBA that whatever you say in your next Print (or Write) statement, you want it to be written to the same line. For example:

Sub SimpleWriteToTextFile2()
    Open "C:\Users\Ryan\Documents\wellsr\DemoFile.txt" For Output As #1
        Print #1, "This is cell B2: ",
        Print #1, Range("B2")
    Close #1
End Sub

Even though there are two different Print statements, the output shows up on the same line.

If you remove the comma, like this:

Sub SimpleWriteToTextFile3()
    Open "C:\Users\Ryan\Documents\wellsr\DemoFile.txt" For Output As #1
        Print #1, "This is cell B2: "
        Print #1, Range("B2")
    Close #1
End Sub

The output shows up on 2 different lines:

It’s amazing the difference one character, like a comma, can make when programming!

VBA Closing your File

This is the easiest thing we’ll talk about, but it’s probably the most important.

When you’re done writing to your file, close it! Not doing this can inadvertently lock up your text file. To close your file, just use the Close statement followed by your #FileNumber.

Close #1

You’ll notice in my main example, I included a VBA Shell statement to open my file when I was done with it. You can do the same if you’d like!

Application Ideas - VBA Write Text File

File I/O is one of the most powerful tasks to master with any programming language - including VBA. Once you’re done writing your text files, you can loop through all the files in a folder to manipulate the data however you want.

If you haven’t already done so, join our VBA Insiders using the form below. After that, share this article on social media and follow me on Twitter for even more great VBA content.