VBA For Excel AfterRefresh 事件

VBA For Excel AfterRefresh Event

提问by Paul Renton

I am using the following QueryTable Inquiry. After the .Refreshexecutes the VBA procedure ends. The inquiry works but I need to execute code after it completes.

我正在使用以下 QueryTable 查询。.Refresh执行后VBA 程序结束。查询有效，但我需要在完成后执行代码。

The .AfterRefreshevent seems to be what I need, but I can't get it to execute.

该.AfterRefresh事件似乎是我需要的，但我无法执行。

With ActiveSheet.QueryTables.Add(Connection:="URL;" & sUrl, Destination:=ActiveSheet.Range("a1"))

             .RefreshStyle = xlOverwriteCells
             .SaveData = True
             .Refresh
             .AfterRefresh (Success)
End With

This is the AfterRefresh sub that does not execute.

这是不执行的 AfterRefresh 子程序。

Sub QueryTable_AfterRefresh(Success As Boolean)

        If Success Then
                 Debug.Print "Success"
        Else
                 Debug.Print "Failed"
        End If
End Sub

What is needed to trigger the subroutine after the query is done? I tried a call to the subroutine after the .Refreshand after the End With but neither worked.

查询完成后需要什么来触发子程序？我尝试.Refresh在 End With之后和之后调用子程序，但都没有奏效。

Thanks.

谢谢。

回答by Peter L.

Please make sure your QueryTable_AfterRefreshsub is placed NOT in the module, but under Sheet / Workbook, the same way as here: https://stackoverflow.com/a/14646261/1953175Moreover, you do not need to call event, remove .AfterRefresh (Success)from your code.

请确保您的QueryTable_AfterRefresh子程序不在模块中，而是在工作表/工作簿下，与此处相同：https: //stackoverflow.com/a/14646261/1953175此外，您不需要调用事件，.AfterRefresh (Success)从您的代码。

回答by Paul Renton

I ran across this same issue recently and it was very difficult to find a good answer. I realize this thread is old, but there is a decent alternative to the other solution posted.

我最近遇到了同样的问题，很难找到一个好的答案。我意识到这个线程已经过时了，但是对于发布的其他解决方案有一个不错的替代方案。

One pattern you can utilize is keeping the QueryTable callback events in a separate Class Moduleinstead of embedded within a worksheet. This allows for more modular, reusable code. It becomes especially useful when your Excel Workbook has multiple QueryTables.

您可以使用的一种模式是将 QueryTable 回调事件保存在单独的类模块中，而不是嵌入到工作表中。这允许更多模块化、可重用的代码。当您的 Excel 工作簿有多个 QueryTables 时，它变得特别有用。

Here is what the class module might look like in a class module called CQtEvents

这是类模块在名为CQtEvents的类模块中的样子

Option Explicit

Private WithEvents mQryTble As Excel.QueryTable
' Add variables you may want to cache here such at the query or connection settings

' Properties
Public Property Set QryTble(ByVal QryTable As QueryTable): Set mQryTble = QryTable:
End Property
Public Property Get QryTble() As QueryTable: Set QryTble = mQryTble:
End Property
' Add other potential properties here

Private Sub Class_Initialize()
    ' Constructor
    MsgBox "CQtEvents init"
End Sub

Private Sub mQryTble_BeforeRefresh(ByVal Cancel as Boolean)
    'Insert logic you want to run before a refresh
End Sub   

Private Sub mQryTble_AfterRefresh(ByVal Success As Boolean)
    'Insert logic you want to run after a refresh

End Sub

The key thing to note above is the WithEventskeyword and the declarations/definitions for BeforeRefresh and AfterRefresh.

上面要注意的关键是WithEvents关键字以及 BeforeRefresh 和 AfterRefresh 的声明/定义。

Below is what the code might look like to leverage the Class Module defined above

下面是利用上面定义的类模块的代码可能看起来像

Option Explicit

Sub RefreshDataQuery()
'Dependencies: Microsoft Scripting Runtime (Tools->References) for Dictionary (HashTable) object

Dim querySheet As Worksheet
Dim classQtEvents As CQtEvents

Set querySheet = Worksheets("QTable")
Set interface = Worksheets("Interface")
Set classQtEvents = New CQtEvents ' Instantiate the Class

Dim qt As QueryTable
Dim qtDict As New Scripting.Dictionary

Set qtDict = UtilFunctions.CollectAllQueryTablesToDict
Set qt = qtDict.Item("Query from fred2")

''' Building SQL Query String '''
qt.CommandText = "Select * From someTable" 

If Not qt Is Nothing Then
    qt.Refresh False ' See link at bottom of post for alternatives to this
Else
    ' ... Error handling code here... 
End If


''' CLEAN UP '''

' Free the dictionary
Set qtDict = Nothing

End Sub

The one caveat with this approach is that the AfterRefresh will not be called if this is run asynchronously and left as is. The reason for this is the reference to the query table will disappear when the module finishes executing, which is likely to finish before the query finishes executing. To get around this, you can run it synchronously by setting

这种方法的一个警告是，如果异步运行并保持原样，则不会调用 AfterRefresh。这样做的原因是当模块完成执行时，对查询表的引用将消失，这很可能在查询完成执行之前完成。为了解决这个问题，您可以通过设置同步运行它

 qt.Refresh False

However, this is not the best approach but will work if you don't mind waiting on the query before any other code in the Sub Module runs. See this post for a really good answer on alternatives to this Excel VBA - QueryTable AfterRefresh function not being called after Refresh completesby KazJaw.

但是，这不是最好的方法，但如果您不介意在子模块中的任何其他代码运行之前等待查询，它就会起作用。有关此Excel VBA 的替代方案的非常好的答案，请参阅此帖子- KazJaw 完成刷新后未调用 QueryTable AfterRefresh 函数。

Hope this helps as this is a good alternative to writing these event handlers embedded in a worksheet

希望这会有所帮助，因为这是编写嵌入工作表中的这些事件处理程序的不错选择

回答by mcw

A github repo that demonstrates the minimum code needed to get this working can be found here.

可以在此处找到演示实现此工作所需的最少代码的 github 存储库。

As mentioned in the other answers, the key factors to ensuring you catch the event are:

正如其他答案中提到的，确保您赶上事件的关键因素是：

1. Declare a global variable of your event-handling class module's type outsideof any subroutines/methods, at the top of a file (I chose the ThisWorkbookfile).

2. Add a Workbook_Openevent handler and instantiate that variable there, so that it is available immediately and will remain in scope(since it's global).

3. At that point, or at any downstream point when you have a QueryTable you're interested in, pass that QueryTable to the global instance to wire up its events.

1. 在任何子例程/方法之外，在文件顶部声明事件处理类模块类型的全局变量（我选择了该ThisWorkbook文件）。

2. 添加一个Workbook_Open事件处理程序并在那里实例化该变量，以便它立即可用并保持在范围内（因为它是全局的）。

3. 此时，或在您拥有感兴趣的 QueryTable 时的任何下游点，将该 QueryTable 传递给全局实例以连接其事件。

(It took me a couple tries to figure this out myself, when someone pointed me in this direction as an answer to this question.)

（当有人向我指出这个方向作为这个问题的答案时，我花了几次尝试自己解决这个问题。）