如何为Excel的prepareForMS查询绑定QueryTable.BeforeRefresh事件？

一、代码放哪儿？ #

必须放在查询加载所在的工作表模块里，别放错到标准模块（比如Module1）。打开方式很简单：右键对应的工作表标签，选「查看代码」就能进入。

二、一步步挂钩prepareForMS的BeforeRefresh事件 #

1. 先声明一个能响应事件的变量 #

在工作表模块的最顶部（所有代码之外的通用区域）加这段代码：

Private WithEvents qtPrepareForMS As QueryTable

这个WithEvents是关键，它让变量能监听QueryTable的事件。

2. 把变量绑定到目标查询 #

加一个工作表激活事件，确保每次打开或切换到该工作表时，变量能准确找到名为prepareForMS的查询：

Private Sub Worksheet_Activate()
    Dim qt As QueryTable
    ' 遍历当前工作表所有查询，匹配名称
    For Each qt In Me.QueryTables
        If qt.Name = "prepareForMS" Then
            Set qtPrepareForMS = qt
            Exit For
        End If
    Next qt
End Sub

要是你的查询是加载到结构化表格（带表头的那种Excel表格）里的，就换成这段代码：

Private Sub Worksheet_Activate()
    Dim lo As ListObject
    For Each lo In Me.ListObjects
        If lo.QueryTable.Name = "prepareForMS" Then
            Set qtPrepareForMS = lo.QueryTable
            Exit For
        End If
    Next lo
End Sub

3. 写刷新前的处理代码 #

事件子程序的名字必须严格按「变量名_事件名」来写，这里就是qtPrepareForMS_BeforeRefresh，代码示例如下：

Private Sub qtPrepareForMS_BeforeRefresh(Cancel As Boolean)
    Dim targetLo As ListObject
    Dim originalColCount As Long
    Dim newColStart As Long
    
    ' 替换成你的查询加载的结构化表格名称
    Set targetLo = Me.ListObjects("prepareForMS_Table")
    
    ' 这里填原查询自带的列数，比如原查询有5列，新增列从第6列开始
    originalColCount = 5
    newColStart = originalColCount + 1
    
    ' 如果确实有新增列，就清除这些列的内容
    If newColStart <= targetLo.ListColumns.Count Then
        targetLo.DataBodyRange.Columns(newColStart To targetLo.ListColumns.Count).ClearContents
    End If
End Sub

要是你不想用结构化表格，直接操作QueryTable的区域，也可以把代码改成操作qtPrepareForMS.ResultRange来清除对应列。

三、要注意的坑 #

• 事件名不能瞎改，必须是「WithEvents变量名_BeforeRefresh」，不然Excel根本识别不到这个事件。
• 查询名称要和Excel里的完全一致，别打错字，你可以去「数据」选项卡→「连接」里查准确名称。
• 要是用Power Query加载的查询，有些版本里QueryTable的名称会自动生成，一定要确认好实际名称。

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