刷新或刷新后如何调用宏按下所有按钮？ [英] How to call macro after Refresh or Refresh All button pressed?

问题描述

最终，我希望在任何人刷新工作簿之后运行宏，具体使用Excel中数据选项卡下的刷新按钮。然而，暂时的，我会感到满意的是，在按下Refresh按钮之后触发BeforeRefresh或AfterRefresh QueryTable事件。

除了提供的文档在微软开发中心网站上，我作为本研究过程的一部分阅读的相关文章包括：

• Excel VBA - QueryTable AfterRefresh功能在刷新完成后未被调用


• VBA for Excel AfterRefresh事件


• 还有其他不太有帮助或相关的帖子，但我没有在这里张贴的声誉。

我我很清楚地遗漏了一些重要的东西（很可能很明显）。这是我到目前为止：

在类模块下（qtclass）

  Option Explicit 
 
 Private WithEvents qt As Excel.QueryTable 
 
 Private Sub qt_AfterRefresh（ByVal Success As Boolean）
 
 MsgBoxqt_AfterRefresh称为成功。 
如果Success = True然后
调用Module2.SlicePivTbl 
 MsgBox如果调用成功。 
 End If 
 
 End Sub 
 
 Private Sub qt_BeforeRefresh（Cancel As Boolean）
 MsgBoxqt_BeforeRefresh called。 
 End Sub 
  

在ThisWorkbook模块下

  Private Sub Workbook_Open（）
 
 Dim qtevent As qtclass 
 Dim qt As QueryTable 
设置qt = ThisWorkbook.Worksheets（数据基金）ListObjects（1）.QueryTable 
设置qtevent =新的qtclass 
 
 End Sub 
  

我已经尝试了特定工作表下的第二个代码块的变体，但还没有找到任何有用的代码块。我需要在Worksheet模块中以某种方式调暗QueryTable吗？对于我所缺少的任何建议或想法将不胜感激。

解决方案

您没有将查询表连接到类实例。
修改qtclass

  Option Explicit 
 
私有WithEvents qt As Excel.QueryTable 
公共属性集HookedTable（q As Excel.QueryTable）
设置qt = q 
结束属性
 
 Private Sub qt_AfterRefresh（ByVal Success As Boolean）
 
 MsgBoxqt_AfterRefresh调用成功。 
如果Success = True然后
调用Module2.SlicePivTbl 
 MsgBox如果调用成功。 
 End If 
 
 End Sub 
 
 Private Sub qt_BeforeRefresh（Cancel As Boolean）
 MsgBoxqt_BeforeRefresh called。 
 End Sub 
  

新的ThisWorkbook代码：

  Dim qtevent As qtclass 
 Private Sub Workbook_Open（）
 
设置qtevent =新的qtclass 
设置qtevent.HookedTable = ThisWorkbook。工作表（Data-Fund）。ListObjects（1）.QueryTable 
 
 End Sub 
  

$ b $请注意，这是非常紧密的耦合。如果您要在课堂中提出事件并声明您的qtevent变量WithEvents，那将会更加可重用。

Ultimately, I would like to run a macro after anyone refreshes the workbook, specifically using the Refresh button under the Data tab in Excel. For the time being, however, I would be satisfied just getting the BeforeRefresh or AfterRefresh QueryTable events to fire upon pressing the Refresh button.

In addition to the 'documentation' offered on the Microsoft Dev Center website, the relevant posts I have read as part of this research process include:

• Excel VBA - QueryTable AfterRefresh function not being called after Refresh completes
• VBA For Excel AfterRefresh Event
• There are other less helpful or relevant posts but I lack the reputation to post them here.

I am clearly missing something important (and most likely obvious). Here is what I have thus far:

Under Class Modules (qtclass)

Option Explicit

Private WithEvents qt As Excel.QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

    MsgBox "qt_AfterRefresh called sucessfully."
    If Success = True Then
        Call Module2.SlicePivTbl
        MsgBox "If called succesfully."
    End If

End Sub

Private Sub qt_BeforeRefresh(Cancel As Boolean)
    MsgBox "qt_BeforeRefresh called."
End Sub

Under the ThisWorkbook module

Private Sub Workbook_Open()

    Dim qtevent As qtclass
    Dim qt As QueryTable
    Set qt = ThisWorkbook.Worksheets("Data-Fund").ListObjects(1).QueryTable
    Set qtevent = New qtclass

End Sub

I have tried variations of the second code block under specific worksheets as well, but have yet to find anything that works. Do I need to somehow dim the QueryTable in question in the Worksheet module? Any suggestions or thoughts on what I am missing would be greatly appreciated.

解决方案

You haven't actually connected the querytable to the class instance. Revised qtclass

Option Explicit

Private WithEvents qt As Excel.QueryTable
Public Property Set HookedTable(q As Excel.QueryTable)
    Set qt = q
End Property

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

    MsgBox "qt_AfterRefresh called sucessfully."
    If Success = True Then
        Call Module2.SlicePivTbl
        MsgBox "If called succesfully."
    End If

End Sub

Private Sub qt_BeforeRefresh(Cancel As Boolean)
    MsgBox "qt_BeforeRefresh called."
End Sub

New ThisWorkbook code:

Dim qtevent As qtclass
Private Sub Workbook_Open()

    Set qtevent = New qtclass
    Set qtevent.HookedTable = ThisWorkbook.Worksheets("Data-Fund").ListObjects(1).QueryTable

End Sub

Note that this is quite closely coupled. It would be more re-usable if you were to raise events in the class and declare your qtevent variable WithEvents.

这篇关于刷新或刷新后如何调用宏按下所有按钮？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！