Excel 2016中QueryTable刷新等待及工作表保护问题求助

针对Excel 2016中关联PowerQuery QueryTable的ListObject刷新后保护工作表的问题，以下是两种可行的VBA方案，解决BackgroundQuery属性失效导致的提前保护中断刷新问题：

方案一：利用QueryTable的AfterRefresh事件（推荐） #

PowerQuery的QueryTable在本地数据源场景下，同步刷新设置可能不生效，但AfterRefresh事件会在刷新完成后可靠触发，通过事件标记刷新状态是最稳妥的方式。

步骤1：在工作表模块中绑定事件 #

打开包含LO1的工作表对应的VBA模块（右键工作表标签→查看代码），添加以下代码：

' 用于标记刷新是否完成的公共变量
Public qtRefreshComplete As Boolean

Private Sub Worksheet_Activate()
    ' 绑定QueryTable的AfterRefresh事件
    Dim qt As QueryTable
    Set qt = Me.ListObjects("LO1").QueryTable
    ' 必须开启异步刷新才能触发AfterRefresh事件
    qt.BackgroundQuery = True
End Sub

' QueryTable刷新完成后触发的事件
Private Sub qt_AfterRefresh(ByVal Success As Boolean)
    qtRefreshComplete = True
    ' 可选：如果刷新失败可以添加提示
    ' If Not Success Then MsgBox "刷新失败"
End Sub

步骤2：编写主执行过程 #

在标准模块中添加以下代码（插入→模块）：

Sub RefreshAndProtect()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim qt As QueryTable
    
    ' 替换为你的工作表名和ListObject名称
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set lo = ws.ListObjects("LO1")
    Set qt = lo.QueryTable
    
    ' 初始化刷新完成标记
    ws.qtRefreshComplete = False
    
    ' 先取消工作表保护（如果已保护）
    If ws.ProtectContents Then
        ' 替换为你的保护密码，无密码可省略Password参数
        ws.Unprotect Password:="yourSecurePassword"
    End If
    
    ' 触发QueryTable刷新
    qt.Refresh BackgroundQuery:=True
    
    ' 等待刷新完成，添加超时防止死循环
    Dim startTime As Double
    startTime = Timer
    Do While Not ws.qtRefreshComplete
        DoEvents ' 释放CPU，避免Excel假死
        ' 超时设置为30秒，可根据数据量调整
        If Timer - startTime > 30 Then
            MsgBox "刷新超时，请检查数据或延长超时时间"
            Exit Sub
        End If
    Loop
    
    ' 刷新完成，保护工作表（可根据需求调整保护选项）
    ws.Protect Password:="yourSecurePassword", _
        AllowFiltering:=True, ' 允许用户筛选表格
        AllowSorting:=True   ' 允许用户排序表格
        ' 其他常用选项：AllowFormattingCells:=True, AllowInsertingColumns:=False等
End Sub

方案二：监控ListObject行计数稳定性 #

如果事件绑定存在问题，可以通过监控ListObject的行数量变化来判断刷新是否完成——PowerQuery刷新时会先清空表格再插入新数据，当行计数连续多次稳定不变时，说明刷新已完成。

Sub RefreshAndProtect_Alternative()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim qt As QueryTable
    Dim prevRowCount As Long
    Dim currentRowCount As Long
    Dim stableRowCount As Integer
    Dim startTime As Double
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set lo = ws.ListObjects("LO1")
    Set qt = lo.QueryTable
    
    ' 取消工作表保护
    If ws.ProtectContents Then
        ws.Unprotect Password:="yourSecurePassword"
    End If
    
    ' 触发刷新
    qt.Refresh BackgroundQuery:=True
    
    ' 监控行计数稳定性，连续3次相同则判定为刷新完成
    startTime = Timer
    stableRowCount = 0
    prevRowCount = lo.ListRows.Count
    Do
        DoEvents
        currentRowCount = lo.ListRows.Count
        If currentRowCount = prevRowCount Then
            stableRowCount = stableRowCount + 1
        Else
            stableRowCount = 0
            prevRowCount = currentRowCount
        End If
        ' 超时30秒或连续3次行计数稳定则退出循环
        If stableRowCount >= 3 Or Timer - startTime > 30 Then
            Exit Do
        End If
    Loop
    
    ' 检查是否超时
    If Timer - startTime > 30 Then
        MsgBox "刷新超时"
        Exit Sub
    End If
    
    ' 保护工作表
    ws.Protect Password:="yourSecurePassword", AllowFiltering:=True, AllowSorting:=True
End Sub

为什么之前的方法失效？ #

在Excel 2016的PowerQuery场景中，针对本地数据源的QueryTable，BackgroundQuery:=False的同步刷新设置可能不会生效，Refreshing属性也无法正确返回当前刷新状态，导致代码提前执行保护操作中断刷新。而AfterRefresh事件是PowerQuery刷新流程中明确触发的回调，行计数监控则通过数据变化间接判断刷新完成状态，均能避开上述问题。

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