VPN环境下多用户同时从Excel源文件更新本地工作簿的问题求助

问题根源 #

你当前的VBA代码使用Workbooks.Open直接打开共享文件夹中的Source.xlsm，默认会以独占模式锁定文件，这就是多用户同时操作时出现凭证错误、无法刷新的核心原因——第一个用户打开后，文件被独占，后续用户无法正常访问。

方案1：改用Power Query（推荐） #

既然你已经确认ExcelThatWorks.xlsm通过Power Query实现了多用户并发，这是最稳定的方案：Power Query通过OLEDB连接读取数据，不会独占源文件，天然支持多用户同时访问。

操作步骤 #

1. 在你的NewData.xlsm中建立Power Query连接：
   • 点击「数据」选项卡 → 「获取数据」→ 「从文件」→ 「从Excel工作簿」
   • 选择共享路径下的Source.xlsm，选择需要的工作表（RAW、Data），设置为「仅创建连接」或直接加载到对应工作表
2. 替换原有刷新宏为Power Query刷新代码（可直接复用你提供的示例）：

Public Sub UpdatePowerQueries()
' Macro to update my Power Query script(s)
Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
    lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
    If Err.Number <> 0 Then
        Err.Clear
        Exit For
    End If
    If lTest > 0 Then cn.Refresh
Next cn
End Sub

3. 将刷新按钮绑定到这个UpdatePowerQueries宏即可。

方案2：修改现有VBA代码为只读共享模式 #

如果不想切换到Power Query，可以修改Workbooks.Open的参数，强制以只读、共享模式打开源文件，避免独占锁定：

优化后的完整VBA代码 #

Option Explicit

Public Sub Stop_ScreenUpdateUpdate()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False ' 关闭打开文件时的系统提示
    
    Dim s As Workbook
    ' 以只读+共享模式打开源文件，避免独占锁定
    Set s = Workbooks.Open(Filename:="\\Full\Shared\Folder\Path\Source.xlsm", _
                          ReadOnly:=True, _
                          IgnoreReadOnlyRecommended:=True, _
                          UpdateLinks:=xlUpdateLinksNever)
    
    Dim d As Workbook
    Set d = ThisWorkbook
    
    ' 复制RAW工作表数据
    With s.Sheets("RAW").UsedRange
        d.Sheets("RAW").Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    
    ' 复制Data工作表数据
    With s.Sheets("Data").Range("A2:J200")
        d.Sheets("Data").Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    
    ' 关闭源文件（无需保存）
    s.Close SaveChanges:=False
    
    ' 刷新透视表（指定工作表避免ActiveSheet的潜在问题）
    d.Sheets("透视表所在工作表名称").PivotTables("PivotTable1").RefreshTable
    MsgBox "Data has been refreshed!"
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

关键参数说明 #

• ReadOnly:=True：强制以只读模式打开，避免写入锁定
• IgnoreReadOnlyRecommended:=True：跳过Excel的只读推荐提示框
• UpdateLinks:=xlUpdateLinksNever：禁止更新源文件内部的链接，加快打开速度

额外注意事项 #

• 确保所有用户对共享文件夹拥有只读权限，避免意外修改Source.xlsm
• 若仍出现锁定问题，可检查共享文件夹权限设置：右键文件夹→属性→共享→高级共享→权限，确认「读取」权限已授予所有用户

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