Excel Web查询RefreshPeriod致AfterRefresh事件仅触发一次及名称引用问题

我之前处理Web查询自动刷新的时候，也踩过这两个一模一样的坑！给你整理了亲测有效的解决方案：

一、解决.AfterRefresh事件仅首次触发的问题 #

默认直接在工作表模块绑定QueryTable事件的方式，确实只会在手动刷新或首次自动刷新时触发，后续定时刷新不会触发。这时候需要用类模块来持久化事件绑定，步骤如下：

1. 插入一个类模块，命名为clsQueryEvents，然后在类模块里写入以下代码：

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
    ' 这里写你要调用的一系列过程
    If Success Then
        Call YourProcedure1
        Call YourProcedure2
        ' 更多自定义过程...
    End If
End Sub

2. 在标准模块（比如Module1）里声明一个模块级的类实例变量，确保它不会在过程结束后被销毁：

Private qtEvents As clsQueryEvents

Sub InitializeQueryEvents()
    Dim targetQT As QueryTable
    ' 调用后面的查找函数定位目标QueryTable
    Set targetQT = FindTargetQueryTable(Sheet1, "YourOriginalQTName")
    
    If Not targetQT Is Nothing Then
        Set qtEvents = New clsQueryEvents
        Set qtEvents.qt = targetQT
        ' 设置刷新周期（比如10分钟）
        targetQT.RefreshPeriod = 10
        ' 手动触发第一次刷新
        targetQT.Refresh BackgroundQuery:=True
    End If
End Sub

3. 打开工作簿时自动初始化事件绑定，在ThisWorkbook模块里添加：

Private Sub Workbook_Open()
    Call InitializeQueryEvents
End Sub

这样设置后，每次自动刷新完成后，qt_AfterRefresh事件都会被触发，就能自动调用你的自定义过程了。

二、解决QueryTable名称被自动加后缀的问题 #

Excel确实会在重复创建同名QueryTable时自动添加_1、_2这类后缀，所以不能硬编码名称查找。我们可以写一个通用函数，通过原始名称前缀或者查询的连接字符串/URL来精准定位目标QueryTable：

方法1：按原始名称前缀查找 #

Function FindTargetQueryTable(ws As Worksheet, originalName As String) As QueryTable
    Dim qt As QueryTable
    For Each qt In ws.QueryTables
        ' 匹配原始名称开头的QueryTable（比如originalName是"WebQuery"，就匹配"WebQuery"、"WebQuery_1"等）
        If Left(qt.Name, Len(originalName)) = originalName Then
            Set FindTargetQueryTable = qt
            Exit Function
        End If
    Next qt
    ' 如果没找到，返回Nothing
    Set FindTargetQueryTable = Nothing
End Function

方法2：按查询的URL/连接字符串查找（更精准） #

如果你的Web查询是固定URL，可以直接匹配连接字符串：

Function FindTargetQueryTableByURL(ws As Worksheet, targetURL As String) As QueryTable
    Dim qt As QueryTable
    For Each qt In ws.QueryTables
        ' 检查连接字符串是否包含目标URL
        If InStr(qt.Connection, targetURL) > 0 Then
            Set FindTargetQueryTableByURL = qt
            Exit Function
        End If
    Next qt
    Set FindTargetQueryTableByURL = Nothing
End Function

用这个函数替代硬编码名称的方式，就能精准找到目标QueryTable，不管Excel加了什么后缀。

额外注意事项 #

• 确保qtEvents是模块级变量，如果放在过程里声明，过程结束后变量会被销毁，事件绑定也会失效
• 如果工作簿里有多个Web查询，要注意区分目标QueryTable，用URL匹配的方式最稳妥
• 在AfterRefresh事件里最好加Success判断，避免刷新失败时执行不必要的过程

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