Excel文件中启用修订功能的宏问题及运行时错误1004解决

问题场景 #

我用Power Query从主数据源生成数据表，需要将每个数据表导出为独立工作簿分发给用户本地编辑（不改动原数据源）。导出文件的功能正常，但在宏中调用HighlightChangesOptions开启修订追踪时，触发运行时错误1004。

解决方法 #

尝试使用.ExclusiveAccess方法会报错，最终通过在SaveAs操作中添加AccessMode:=xlShared参数，成功激活修订功能。

完整宏代码 #

Sub CopyTablesToNewFiles_DeleteConnections_TrackChanges()
    Dim wbSource, wbNew As Workbook
    Dim ws, wsNew As Worksheet
    Dim savePath, currentDate, wsName, Filename As String

    currentDate = Format(Date, "YYYYMMDD")

    Set wbSource = ThisWorkbook

    For Each ws In wbSource.Worksheets
        ws.Copy
        Set wbNew = ActiveWorkbook
        
            '移除与原查询的连接
            Do While wbNew.Connections.Count > 0
            wbNew.Connections.Item(ActiveWorkbook.Connections.Count).Delete
            Loop
            
            '将表格转换为普通区域以支持修订追踪（每个工作表仅含一个表格）
            Set wsNew = ActiveSheet
            For Each objList In wsNew.ListObjects
                objList.Unlist
            Next objList
            
        savePath = "C:\Path\Test\"

    With wbNew
        '.ExclusiveAccess '- 会触发运行时错误1004
        '通过在SaveAs中添加xlShared参数激活修订追踪
        .SaveAs Filename:=savePath & currentDate & "_" & ws.Name & ".xlsx", AccessMode:=xlShared
        .HighlightChangesOptions When:=xlAllChanges
        .ListChangesOnNewSheet = False
        .HighlightChangesOnScreen = True
    End With
    
    wbNew.Save
        wbNew.Close
    Next ws

    'wbSource.Close
End Sub

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