首先，你的问题根源在于ActiveWorkbook.RefreshAll会一次性刷新所有外部连接和数据透视表，当数据源是共享在OneDrive的文件时，刷新连接会持续占用数据源文件，导致其他人无法编辑。我们需要调整代码，只刷新仪表盘里的数据透视表，并且优化数据源的访问方式来避免锁定。

修改后的VBA代码 #

下面是针对你的需求优化的代码，适合无VBA基础的用户：

' 全局变量，用于存储定时任务的时间，方便停止刷新
Dim alertTime As Date

Public Sub StartAutoRefresh()
    ' 设置刷新间隔（这里是5分钟，可自行修改）
    Dim refreshInterval As String
    refreshInterval = "00:05:00" ' hh:mm:ss格式
    
    ' 先执行一次刷新
    RefreshDashboardPivots
    
    ' 设置下一次刷新的时间
    alertTime = Now + TimeValue(refreshInterval)
    Application.OnTime alertTime, "StartAutoRefresh"
    MsgBox "自动刷新已启动，每" & Left(refreshInterval, 5) & "分钟刷新一次", vbInformation
End Sub

Public Sub StopAutoRefresh()
    ' 停止定时刷新任务
    On Error Resume Next ' 防止任务已不存在时报错
    Application.OnTime alertTime, "StartAutoRefresh", , False
    On Error GoTo 0
    MsgBox "自动刷新已停止", vbInformation
End Sub

Private Sub RefreshDashboardPivots()
    Dim ws As Worksheet
    Dim pt As PivotTable
    
    ' 遍历当前工作簿的所有工作表
    For Each ws In ThisWorkbook.Worksheets
        ' 遍历工作表中的所有数据透视表
        For Each pt In ws.PivotTables
            ' 关键：设置数据透视表刷新时不保留连接，避免锁定数据源
            pt.PivotCache.RefreshOnFileOpen = False
            pt.PivotCache.BackgroundQuery = True ' 后台刷新，不阻塞操作
            
            ' 刷新数据透视表
            pt.RefreshTable
        Next pt
    Next ws
End Sub

代码说明（适合新手理解） #

• StartAutoRefresh：启动定时刷新的入口，设置刷新间隔后，会先执行一次刷新，然后定时重复。
• StopAutoRefresh：用于停止定时任务，避免关闭Excel后任务仍在后台运行。
• RefreshDashboardPivots：核心逻辑，只刷新当前仪表盘工作簿里的所有数据透视表，并且做了两个关键优化：
  • pt.PivotCache.RefreshOnFileOpen = False：禁止打开文件时自动刷新，减少对数据源的访问次数。
  • pt.PivotCache.BackgroundQuery = True：启用后台刷新，刷新时你仍可以操作仪表盘，同时避免长时间占用数据源文件。

使用步骤 #

1. 打开你的仪表盘工作簿（已另存为启用宏的格式.xlsm）。
2. 按下Alt + F11打开VBA编辑器。
3. 在左侧“工程资源管理器”中，右键点击你的工作簿名称 → 插入 → 模块。
4. 将上面的代码粘贴到模块窗口中。
5. 返回Excel界面，按下Alt + F8，选择StartAutoRefresh并点击“运行”，即可启动定时刷新。
6. 需要停止时，同样按Alt + F8运行StopAutoRefresh。

额外注意事项 #

• 确保你的数据透视表的数据源设置为只读访问：如果数据源是另一个Excel文件，在设置数据透视表数据源时，打开文件选择“只读”模式，这样刷新时不会锁定数据源文件。
• OneDrive同步问题：如果数据源文件正在同步，刷新可能会短暂延迟，这是正常现象，后台刷新会自动处理。

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