如何在受保护工作表中用VBA刷新Power Query后重新保护？

问题原因 #

第一段代码报错的核心原因是ActiveWorkbook.RefreshAll默认是异步执行的——代码会在触发刷新后立刻执行工作表保护操作，但此时Power Query还在后台刷新数据，导致刷新过程中尝试修改已被重新保护的单元格，从而抛出错误。第二段代码因为没有后续的保护步骤，所以刷新能正常完成，但无法自动恢复工作表保护。

解决方案 #

以下两种方法均可实现“取消保护→刷新→重新保护”的完整流程：

方法1：设置Power Query为同步刷新

将所有Power Query设置为同步模式，确保刷新完成后再执行保护操作：

Sub RefreshAndProtect()
    Dim targetWs As Worksheet
    Dim queryObj As WorkbookQuery
    
    ' 绑定目标工作表
    Set targetWs = ThisWorkbook.Worksheets("Sheet2")
    ' 取消工作表保护（若有密码，添加参数：Password:="你的密码"）
    targetWs.Unprotect
    
    ' 将所有Power Query设置为同步刷新，避免异步导致提前保护
    For Each queryObj In ThisWorkbook.Queries
        queryObj.BackgroundQuery = False
    Next queryObj
    
    ' 执行全工作簿刷新
    ThisWorkbook.RefreshAll
    
    ' 重新保护工作表（若有密码，添加参数：Password:="你的密码"）
    targetWs.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

方法2：等待所有刷新完成后再保护

保留异步刷新模式，通过循环检查连接状态，等待所有刷新任务结束后再执行保护：

Sub RefreshAndProtect()
    Dim targetWs As Worksheet
    Dim connObj As WorkbookConnection
    
    Set targetWs = ThisWorkbook.Worksheets("Sheet2")
    targetWs.Unprotect ' 有密码则添加Password参数
    
    ' 触发全工作簿刷新
    ThisWorkbook.RefreshAll
    
    ' 循环等待所有数据连接刷新完成
    For Each connObj In ThisWorkbook.Connections
        Do While connObj.OLEDBConnection.Refreshing
            DoEvents ' 释放系统资源，避免Excel假死
        Loop
    Next connObj
    
    ' 重新保护工作表
    targetWs.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

注意事项 #

• 如果工作表保护设置了密码，需要在Unprotect和Protect方法中添加Password:="你的密码"参数，例如targetWs.Unprotect Password:="123456"。
• 方法1会修改Power Query的默认刷新模式，若需要保留异步特性，优先选择方法2。

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