[Excel VBA] How to Create and Write to a Text File (.txt) Using FSO

In VBA macros, it is common to output results as a simple text file (.txt) rather than in Excel format, especially when integrating with other systems.

Using the FileSystemObject (FSO), you can create new text files and write content to them using a clear, object-oriented approach. This article explains how to use the CreateTextFile method and the TextStream object.

[Preparation] Reference Setting

To use FSO effectively, it is recommended to open the VBA Editor, go to Tools > References, and check “Microsoft Scripting Runtime”.

Complete VBA Code

The following code creates a new text file named “Log.txt” in the same folder as the macro workbook and writes two lines of text.

' Reference: Microsoft Scripting Runtime
Sub CreateAndWriteTextFile()

    ' Declare variables
    Dim fso As New FileSystemObject
    Dim textStreamObj As TextStream
    Dim filePath As String
    
    '--- Settings ---
    ' Path of the text file to be created
    filePath = ThisWorkbook.Path & "\Log.txt"
    '--- End Settings ---

    '--- 1. Create a new text file and get the TextStream object ---
    ' Set the second argument (Overwrite) to True to overwrite if the file already exists
    Set textStreamObj = fso.CreateTextFile(Filename:=filePath, Overwrite:=True)

    '--- 2. Write to the file using the TextStream object ---
    With textStreamObj
        ' .WriteLine writes the string followed by a newline character
        .WriteLine "--- Processing Log ---"
        .WriteLine "Report Created: " & Now()
        
        ' .Write writes the string without adding a newline
        .Write "Status: "
        .Write "Success"
        
        ' .Close ensures all data is written and the file is properly closed
        .Close
    End With
    
    ' Release objects
    Set textStreamObj = Nothing
    Set fso = Nothing

    MsgBox "Text file creation and writing complete."

End Sub

Key Points of the Code

1. Creating the File: .CreateTextFile

Set myStream = fso.CreateTextFile(Filename:="FilePath", Overwrite:=True)

The CreateTextFile method of the fso object creates a new file at the specified path and returns a TextStream object to manipulate that file.

• Filename: The full path of the file to create.
• Overwrite (Optional): If True, it overwrites an existing file of the same name. If False (default) and the file exists, an error occurs.

2. Writing to the File: TextStream Object

The TextStream object provides methods for writing text:

• .WriteLine “String”: Writes the specified string and automatically adds a newline character. This is very useful for writing data line by line.
• .Write “String”: Writes the string without adding a newline. Use this when you want to concatenate multiple elements on the same line.
• .Close: This is the most important method. Data written with Write or WriteLine is initially held in memory. Executing .Close commits all content to the file and closes it. Failure to call this may result in an empty file.

[Advanced] Exporting a Cell Range to a Text File

You can apply this technique to export data from an Excel sheet to a text file.

Sub ExportRangeToTextFile()
    Dim fso As New FileSystemObject
    Dim ts As TextStream
    Dim dataRange As Range
    Dim cell As Range
    
    Set dataRange = Range("A1:C10")
    Set ts = fso.CreateTextFile(ThisWorkbook.Path & "\ExportData.txt", True)
    
    ' Loop through each cell in the range
    For Each cell In dataRange
        ' Write cell value followed by a Tab character
        ts.Write cell.Value & vbTab 
        
        ' If it is the last column in the range, add a newline
        If cell.Column = dataRange.Column + dataRange.Columns.Count - 1 Then
            ts.WriteLine "" 
        End If
    Next cell
    
    ts.Close
    MsgBox "Data export complete."
End Sub

Summary

The steps to create and write to a text file using FSO are:

1. Use fso.CreateTextFile to create the file and obtain a TextStream object.
2. Use .WriteLine (with newline) or .Write (without newline) to write content.
3. Always call .Close to finalize the writing process.

This is a powerful technique for outputting logs or creating data files for other programs to process.