In Excel VBA, use Open filePath For Output As #fileNo to create a text file or replace its contents, then write lines with Print # and finish with Close #. To add lines without clearing existing content, use For Append instead.
How VBA Opens a Text File
The VBA Open statement connects your macro to a file using a file number. For sequential text output, the usual pattern is to get an available number with FreeFile, open the path in Output or Append mode, send text with Print #, and close the number when finished.
Output creates the file if it does not exist. If it already exists, VBA truncates it: its previous contents are removed as the file is opened. That behavior is useful for generating a fresh report, but it makes a fixed output path risky if an existing file matters. Append preserves the current contents and positions new output at the end.
The file number is not the same as a file name. It is a temporary handle VBA uses to direct each Print # operation to the file opened under that number. FreeFile is safer than choosing a number yourself, especially in a larger macro that may have other files open.
A Basic Writing Macro
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste a procedure into the module. On a Mac, use Option+F11 to open the editor (some keyboards also require Fn); the VBA approach is similar, but file paths and available automation components can differ.
This example writes two lines beside the workbook. The workbook must already be saved so that ThisWorkbook.Path contains a folder path, and that folder must be writable.
Sub WriteTextFile()
Dim fileNo As Integer
Dim filePath As String
Dim fileIsOpen As Boolean
Dim errorText As String
On Error GoTo WriteFailed
' Save the text file in the workbook's folder.
filePath = ThisWorkbook.Path & Application.PathSeparator & "report.txt"
fileNo = FreeFile
Open filePath For Output As #fileNo
fileIsOpen = True
Print #fileNo, "Export created: " & Format$(Now, "yyyy-mm-dd hh:nn:ss")
Print #fileNo, "Status: complete"
Close #fileNo
fileIsOpen = False
MsgBox "Wrote " & filePath, vbInformation
Exit Sub
WriteFailed:
errorText = Err.Description
If fileIsOpen Then Close #fileNo
MsgBox "The text file could not be written: " & errorText, vbExclamation
End Sub
Save a copy of the workbook before running a macro. VBA changes cannot be reversed with Ctrl+Z, and this particular procedure replaces an existing report.txt in the workbook folder. The macro does not need to change worksheet cells to write the external file.
Choose Output or Append
Pick the mode based on what should happen to a file that is already there:
- Use Output for a newly generated export, a refreshed log, or a report that should contain only the current run. It replaces the old file contents.
- Use Append for a running log or a file to which each run contributes additional lines. Existing contents remain in place.
- Use Input when the macro needs to read an existing sequential text file rather than write one.
To append, change only the mode in the Open statement. For example, this small procedure adds a timestamped line to a log beside the saved workbook:
Sub AppendLogLine()
Dim fileNo As Integer
Dim filePath As String
' Append a new entry instead of replacing the log.
filePath = ThisWorkbook.Path & Application.PathSeparator & "activity.txt"
fileNo = FreeFile
Open filePath For Append As #fileNo
Print #fileNo, Format$(Now, "yyyy-mm-dd hh:nn:ss") & " - Macro completed"
Close #fileNo
End Sub
Appending does not repair an incomplete final line already in the file. If the existing file ends without a line break, the first appended text may continue on that same line. If line-by-line separation is essential, ensure the file is created with complete lines or inspect and handle its final character using a method suited to the file format.
Writing Lines, Values, and Delimited Data
Each Print # statement without a trailing semicolon or comma ends with a line break. That makes it convenient for plain-text reports: write a heading, then write one record per statement. For example, inside an open output file, Print #fileNo, "Name: " & employeeName writes a labeled line. The comma after the file number is part of the VBA statement syntax; a comma inside the quoted text is ordinary text.
For a delimited export, build each line deliberately. A tab-delimited line can be assembled with vbTab, while comma-separated text can be assembled with commas. But simply joining fields with commas is not enough to create fully reliable CSV: a field containing a comma, quotation mark, or line break needs correct CSV quoting and escaping. If another system expects a strict CSV format, account for those cases rather than assuming every value is a simple word or number.
Write # is another VBA statement, but it is not just a synonym for Print #. It writes values in a delimited, quoted format designed to work with VBA’s Input # statement. Use it when that paired VBA read/write format is useful. For a human-readable text report, Print # usually gives you more direct control over the wording and layout.
Convert values to the presentation you want before writing them. For instance, use Format$ for a timestamp or a fixed date pattern rather than relying on a reader’s assumptions about regional date formats. A text file does not retain Excel cell formatting, formulas, or workbook features; it stores the characters your code sends to it.
Choosing and Building a File Path
The file path determines where the output goes. A full Windows path such as C:\Exports\report.txt is clear, but the Exports folder must exist first. The basic Open statement does not create missing parent folders. A relative path can be harder to reason about because its destination depends on the current directory, so specifying a deliberate folder is generally clearer.
Using ThisWorkbook.Path places the file in the folder containing the workbook that holds the macro. If that workbook has not been saved, its path is empty, so the constructed destination may not be what you intended. Save the workbook first or set filePath to a known full path. Application.PathSeparator supplies the path separator Excel reports for its platform, which helps when building paths for both Windows and Mac; it does not make every Windows folder path portable to a Mac.
Before writing, consider whether the chosen name could overwrite a valuable file. A timestamp in the name can preserve separate exports, while a confirmation prompt can give a user a chance to cancel. If a user selects a destination through an Excel file dialog, check for its cancellation result before passing the selected path to Open.
Errors, Permissions, and Safe File Handling
File operations can fail for reasons unrelated to VBA syntax. The destination folder may not exist, the account may lack write permission, the file may be read-only, or another program may have locked it. A workbook saved to a protected location can also behave differently from one saved in a user-writable folder. Use an error handler when a failed export should produce a useful message rather than stopping abruptly.
The first example uses On Error GoTo to catch a failure and closes the file if it had already been opened. This matters because leaving a file handle open can interfere with later operations in the same Excel session. The fileIsOpen flag prevents the handler from attempting to close a handle when the error occurred before the Open statement succeeded.
For a more protective workflow, check whether the destination exists before using Output, and ask before replacing it. Another option is to write to a temporary name and move the completed file into place after all output succeeds. Those approaches reduce the chance of losing an earlier export if a later step fails, although they require additional file-management code and appropriate permissions.
Do not treat a successful Open as proof that the output is complete. If a macro writes hundreds of records, errors can occur partway through. Close the file in a cleanup path, report the failure, and avoid telling the user that the export completed unless all intended writes finished.
Text Encoding and Platform Differences
The traditional VBA Open statement is suitable for straightforward sequential text files, but it does not provide a simple encoding argument such as “write UTF-8.” Characters outside the system’s active text encoding may not be represented as a modern receiving application expects. This matters for accented names, non-Latin scripts, and files exchanged with web services or systems that specifically require UTF-8.
For UTF-8 output on Windows, a common alternative is the Windows-provided ADODB.Stream component, configured with its Charset property before saving. That approach is different from the basic Open example and should be selected only when encoding requirements justify the extra code. Do not assume the same component or behavior is available in every Excel for Mac environment; check the target platform and test with the receiving application.
Excel for the web cannot run VBA macros. The examples here are for the desktop Excel application. On Mac, VBA file operations are supported, but paths, permissions, and platform-specific components can differ, so use Mac-appropriate paths and test the macro on the computers where it will run.
Frequently Asked Questions
Does VBA Open For Output create a file?
Yes. If the destination file does not exist and its parent folder is available, Open filePath For Output As #fileNo creates it. If the file already exists, Output replaces its contents.
How do I add text without overwriting a file?
Use Open filePath For Append As #fileNo, then write with Print # and close the file. Append preserves prior contents and writes new output at the end.
Why does VBA say Path not found?
Check the full path and confirm that the containing folder exists. The basic Open statement can create the file, but not missing parent directories. Also check for an unsaved workbook if you build the path with ThisWorkbook.Path.
Should I use Print # or Write #?
Use Print # for readable lines whose wording and separators you control. Use Write # when you want VBA’s delimited representation to pair with Input #; it produces a different format.
Can Excel for the web run this VBA code?
No. VBA macros run in desktop Excel, not Excel for the web. Use desktop Excel on Windows or Mac for these examples, and account for platform differences in paths and optional components.
Related
- Excel Meal Prep Template: Plan Meals, Shop Smarter
- Excel Template for Quotation Comparison: Build One
- Excel Task List Template: Set Up and Customize Yours
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Do Excel Macros Work in Google Sheets?No. Excel macros written in VBA do not run in Google Sheets, and converting an Excel workbook does…
- Excel UserForm Date Picker: Options and SetupYes, you can add a date picker to an Excel UserForm, but Excel for Microsoft 365 on Windows…
- VBA Dictionary Set: Create, Add, and Retrieve ItemsIn VBA, use Set to assign a Dictionary object to an object variable, for example Set dict =…
- Can Excel VBA Read Data From a PivotTable?Yes. Excel VBA can read a PivotTable’s values and labels through the PivotTable object model, using methods such…
- Excel UserForm Scrollbar: Setup, Events, and ExamplesTo use a scrollbar on an Excel UserForm, add a ScrollBar control in the Visual Basic Editor, set…
- VBA Excel Delete Range: Clear or Shift Cells?To delete a range in Excel VBA and move the remaining cells up, use Range("B2:D5").Delete Shift:=xlUp; to move…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.