[Excel VBA] How to Append Data to an Existing Text File Using FSO (OpenTextFile)

Introduction

There are often cases where you want to continuously add execution records or results to a single log file every time a macro runs. Instead of creating a new file or overwriting existing content, you need to keep the existing content and append new data to the end.

By using the OpenTextFile method of the FileSystemObject (FSO) in “Append Mode,” you can achieve this process easily and safely.

In this article, I will explain the basic method for adding new lines to an existing text file.

Preparation: Reference Settings

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

Completed VBA Code

The following code appends the date and time the macro was executed to a file named “ActionLog.txt” located in the same folder as the macro workbook.

' Reference: Microsoft Scripting Runtime
Sub AppendLogToFile()

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

    '--- 1. Open text file in Append mode (ForAppending) ---
    ' Setting the 3rd argument (Create) to True creates a new file if it doesn't exist
    Set textStreamObj = fso.OpenTextFile(Filename:=filePath, IOMode:=ForAppending, Create:=True)

    '--- 2. Write a new line to the file using the TextStream object ---
    With textStreamObj
        ' Add a new line at the end using .WriteLine
        .WriteLine "Macro executed. - " & Now()
        
        ' Writing to the file is finalized when .Close is executed
        .Close
    End With
    
    ' Release objects
    Set textStreamObj = Nothing
    Set fso = Nothing

    MsgBox "New record appended to the log file."

End Sub

Every time you run this macro, a new line will be added to the end of “ActionLog.txt”.

Key Points of the Code

OpenTextFile Method

Set myStream = fso.OpenTextFile(Filename, IOMode, [Create])

OpenTextFile opens an existing text file and returns a TextStream object used to manipulate it.

IOMode Argument (Most Important)

The second argument, IOMode, determines how the file is opened. To append data, you must specify the following constant:

Constant	Value	Description
ForReading	1	Opens for reading only.
ForWriting	2	Opens for writing (Note: All existing content is deleted and overwritten).
ForAppending	8	Opens for appending. The file pointer moves to the end automatically.

By specifying IOMode:=ForAppending (or the number 8), you can write new data from the end without deleting existing file content.

Create Argument (Optional)

If you set the third argument Create to True, VBA will automatically create a new empty file if the file does not exist at the specified path. This is convenient for creating a log file for the first time.

Summary

The procedure for appending data to an existing text file using FSO is as follows:

1. Use fso.OpenTextFile("path", ForAppending) to open the file in “Append Mode” and get a TextStream object.
2. Use the .WriteLine method of the TextStream object to write a new line.
3. Finally, always use the .Close method to close the file and finalize the writing.

This is a highly practical technique that can be applied in various situations, such as recording macro execution logs or accumulating multiple processing results into a single file.