Quick Jump
Introduction | Example | Tutorial | Applications | Comments

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!


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

Your time is valuable. It's time to become a VBA expert.

Tutorial - VBA Write Text File

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

Write Range to Text File with VBA

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:

Write to Text File with Print

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:

Write to Text File 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 SimpleWriteToTextFile()
    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.

Write to Text File on Same Line

If you remove the comma, like this:

Sub SimpleWriteToTextFile()
    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:

Write to Text File on New Line

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.

Comments

I hope you’ll subscribe to my email list. Once you’ve subscribed, you’ll get access to a free copy of my Excel Add-In with tons of useful features and you’ll get monthly tutorials where I show you how to do more neat stuff with VBA:)

Share this article with the world on Google+, Twitter and Facebook!

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too


Your time is valuable. It's time to become a VBA expert.

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.