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, 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
Becoming a VBA expert isn't hard
Over 5000 members are improving their VBA skills for free with our email tutorials. Why don't you join them? Our experts share time-saving VBA tips and we'll give you access to our huge macro library - it's sure to speed up your macro development.
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
I’ll explain how it works.
VBA Open File For Output
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
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
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.
, 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.
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.
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 Twitter and Facebook!