When creating files in VBA, instead of overwriting the file as a new one every time, you often want to “continually add new data to an existing file.” Typical examples include operation logs and data collection records.
In this article, I will explain how to use Open For Append to open a file in “Append Mode,” using specific sample code.
Important Note on Where to Place the Code
The code used here is an “Event Macro” that runs automatically when a cell value changes. Therefore, it must be written in a Sheet Module, not a standard module.
- Press Alt + F11 in Excel to open the VBE (Visual Basic Editor).
- From the “Project Explorer” on the left, double-click the sheet you want to record logs for (e.g., Sheet1).
- Paste the code below into the white code window that appears on the right.
Completed VBA Code
Below is the complete code to write in the Sheet Module. I have added timestamp recording and FreeFile for safe file operations to make the log more practical.
Private Sub Worksheet_Change(ByVal Target As Range)
' Declare variables
Dim logFilePath As String
Dim fileNum As Integer
Dim changedCell As Range
' Specify the log file name and path
logFilePath = ThisWorkbook.Path & "\change_log.txt"
' Get an available file number
fileNum = FreeFile
' Open the specified file in "Append Mode"
Open logFilePath For Append As #fileNum
' Loop through cells in case multiple cells are changed at once
For Each changedCell In Target
' Write "Date/Time, Cell Address, New Value" separated by tabs
Print #fileNum, Now() & vbTab & changedCell.Address(False, False) & vbTab & changedCell.Value
Next changedCell
' Close the file
Close #fileNum
End Sub
Explanation of Key Points
1. Worksheet_Change Event
Private Sub Worksheet_Change(ByVal Target As Range) This is a special Sub procedure that automatically runs when a cell value changes on the worksheet where this code is written. The argument Target contains information about the cell (or range) that was changed.
2. Open For Append Mode
Open logFilePath For Append As #fileNum This is the most important point. By specifying For Append as the mode when opening the file, it opens in append mode. This allows you to add new data to the end of the file without erasing existing content. If the file does not exist, a new one is created.
3. Loop Processing for Changed Cells
For Each changedCell In Target Target may contain multiple cells, such as when a user pastes data into a range. Therefore, a For Each loop is used to ensure each changed cell is processed one by one.
4. Writing Log Information
Print #fileNum, Now() & vbTab & changedCell.Address(False, False) & vbTab & changedCell.Value Use Print # to write information to the file. Here, three pieces of information are recorded, separated by tab characters (vbTab):
Now(): The current date and time the change was made.changedCell.Address(False, False): The cell address (e.g., B5).changedCell.Value: The new value entered into the cell.
Sample Execution Result
After setting the code above in the sheet, if you edit a few cells, a file named change_log.txt will be created in the same folder as the Excel file. The content of the file will look like this:
2025/08/09 11:50:38 B3 Product A
2025/08/09 11:50:45 C5 1500
2025/08/09 11:50:51 B3 Product B
You can see that every time a cell is edited, a new log entry is added to the end of the file.
Summary
When you want to add data to an existing file, use the syntax Open "Filename" For Append As #FileNumber.
This Append Mode is useful not only for recording operation logs like in this example but also in various situations where you need to accumulate multiple processing results into a single file. Especially when combined with events like Worksheet_Change, you can build powerful automatic recording systems triggered by user actions.
