求助：修改文件夹监控VBA代码，分栏记录增删事件避免单元格覆盖

Got it, let's get your VBA code to log every file creation in Column A and deletion in Column B—no more overwriting the same cell! Here's what was off with your original code and how to fix it:

Issues in Your Original Code #

• For file creation events, you’re hardcoding Range("A2")—so every new file just overwrites that cell instead of adding a new row.
• For deletion events, Range("B2").End(xlDown).Offset(1, 0) will misbehave if Column B is empty (it’ll jump to the very bottom of the worksheet instead of starting at B2).

Modified Code #

Here’s the updated Select Case block that handles both scenarios correctly:

Select Case objEventObject.Path_.Class
    Case "__InstanceCreationEvent"
        MsgBox "A new file was just created: " & objEventObject.TargetInstance.PartComponent
        ' Find the last used row in Column A, then write to the next row
        Dim lastRowA As Long
        lastRowA = Workbooks("MonitorDirectory").Worksheets("Tabelle1").Cells(Rows.Count, "A").End(xlUp).Row
        ' If Column A is empty (assuming row 1 is a header), start at row 2; else use next row
        If lastRowA = 1 Then
            Workbooks("MonitorDirectory").Worksheets("Tabelle1").Range("A2") = objEventObject.TargetInstance.PartComponent
        Else
            Workbooks("MonitorDirectory").Worksheets("Tabelle1").Cells(lastRowA + 1, "A") = objEventObject.TargetInstance.PartComponent
        End If
        Exit Do
        
    Case "__InstanceDeletionEvent"
        MsgBox "A file was just deleted: " & objEventObject.TargetInstance.PartComponent
        ' Find the last used row in Column B, then write to the next row
        Dim lastRowB As Long
        lastRowB = Workbooks("MonitorDirectory").Worksheets("Tabelle1").Cells(Rows.Count, "B").End(xlUp).Row
        ' If Column B is empty (assuming row 1 is a header), start at row 2; else use next row
        If lastRowB = 1 Then
            Workbooks("MonitorDirectory").Worksheets("Tabelle1").Range("B2") = objEventObject.TargetInstance.PartComponent
        Else
            Workbooks("MonitorDirectory").Worksheets("Tabelle1").Cells(lastRowB + 1, "B") = objEventObject.TargetInstance.PartComponent
        End If
        Exit Do
End Select

Key Improvements #

1. Dynamic Row Detection: We use Cells(Rows.Count, "A").End(xlUp).Row to find the last row with data in Column A (and same logic for B). This works even if there are gaps in your data.
2. Empty Column Handling: The If lastRowA = 1 check assumes your first row is a header (like "Created Files"). If you don’t use headers, just adjust that to If lastRowA = 0 (though Excel typically starts at row 1, so you’d start writing directly to row 1 in that case).
3. Full History Tracking: Each new event gets written to the next available row in the correct column, so you’ll have a complete log of all file changes.

Optional Bonus: Add Timestamps #

If you want to track when each event occurred, you can add a timestamp in an adjacent column by inserting a line like this after writing the file path:

' For creation events (add to Column C)
Workbooks("MonitorDirectory").Worksheets("Tabelle1").Cells(lastRowA + 1, "C") = Now()
' For deletion events (add to Column D)
Workbooks("MonitorDirectory").Worksheets("Tabelle1").Cells(lastRowB + 1, "D") = Now()

内容的提问来源于stack exchange，提问作者Marta__