如何修改Excel宏实现文件被占用时仍追踪所有访问用户

原有宏的失效根因 #

当首个用户打开文件时，Excel会触发文件独占锁定，后续用户打开时只能加载只读副本。原有宏执行完单元格写入后强制调用ActiveWorkbook.Save，因无文件写入权限直接抛出权限错误，导致宏运行中断，既无法把记录写回原文件，也丢失了后续的写入逻辑，自然无法记录被占用状态下的访问用户。

改造核心思路 #

采用双日志通道设计，完全保留原有核心记录能力，从根源绕开Excel文件独占锁定的限制：

• 文件未被锁定、可正常读写打开时：继续按原有逻辑将访问记录写入文件内的隐藏表，完成文件保存
• 文件被锁定、以只读模式打开时：跳过文件内的保存操作，自动将用户名、访问时间追加写入和源文件同目录下的独立外部日志文件。该日志文件不会被Excel的独占锁覆盖，只要用户有共享目录的读写权限，就能正常写入，不存在记录中断问题

改造后完整代码 #

Option Explicit
Private LastRow As Long
Const HIDDEN_SHEET As String = "hidden"
' 定义外部日志文件名，默认和当前Excel文件存放在同一目录
Const LOG_FILE As String = "访问日志.txt"

Private Sub Workbook_Open()
    Dim logPath As String
    Dim fileNum As Integer
    
    Application.EnableEvents = False
    ' 捕获无写入权限的异常，自动切换只读模式的日志写入逻辑
    On Error GoTo ReadOnlyMode
    
    ' 可正常写文件的场景：执行原有隐藏表的记录逻辑
    With Worksheets(HIDDEN_SHEET)
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        .Range("A" & LastRow).Value = Environ("UserName")
        .Range("B" & LastRow).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
    End With
    ActiveWorkbook.Save
    GoTo CleanExit
    
ReadOnlyMode:
    ' 清除之前的权限错误，避免干扰后续操作
    Err.Clear
    ' 拼接外部日志的完整存储路径
    logPath = ActiveWorkbook.Path & "\" & LOG_FILE
    ' 获取系统可用的文件句柄
    fileNum = FreeFile()
    ' 以追加模式打开日志文件，不会覆盖历史记录
    Open logPath For Append As #fileNum
        Print #fileNum, Environ("UserName") & "|" & Format(Now, "dd mmm yyyy hh:mm:ss")
    Close #fileNum

CleanExit:
    Application.EnableEvents = True
End Sub

部署注意事项 #

• 所有需要访问该Excel文件的用户，必须对文件所在的共享文件夹开放修改权限，否则外部日志的追加写入会被系统权限拦截
• 当之前占用文件的用户关闭文件后，后续打开的用户依然会正常向文件内隐藏表写入记录，不会因之前的只读访问出现功能断层
• 外部日志采用“用户名|访问时间”的固定分隔格式，后续可直接导入Excel做汇总分析，和文件内隐藏表的记录格式完全对齐

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