Excel VBA - 刷新完成后未调用 QueryTable AfterRefresh 函数

Excel VBA - QueryTable AfterRefresh function not being called after Refresh completes

提问by Paul Renton

I am developing an Excel (2010+) Application using VBA and have run into an issue where the AfterRefresh event function is not being invoked once the query finishes executing.

我正在使用 VBA 开发 Excel (2010+) 应用程序，并且遇到了一个问题，即一旦查询完成执行，就不会调用 AfterRefresh 事件函数。

I have not been able to find many decent resources or documentation for how to have this event function triggered in a Class Module. I decided to use the Class Module design route instead of putting the event handlers in the worksheet after receiving a response to an earlier question about QueryTables (found here Excel VBA AfterRefresh).

关于如何在类模块中触发此事件函数，我找不到很多不错的资源或文档。我决定使用类模块设计路线，而不是在收到对有关 QueryTables 的早期问题的回复后将事件处理程序放在工作表中（可在此处找到Excel VBA AfterRefresh）。

Here is the code for my Class Module called CQtEvents

这是我的类模块的代码，称为 CQtEvents

Option Explicit

Private WithEvents mQryTble As Excel.QueryTable
Private msOldSql As String

' 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
Public Property Let OldSql(ByVal sOldSql As String): msOldSql = sOldSql:
End Property
Public Property Get OldSql() As String: OldSql = msOldSql:
End Property

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

' Resets the query sql to the original unmodified sql statement
' This method is invoked when the Refresh thread finishes executing
Private Sub mQryTble_AfterRefresh(ByVal Success As Boolean)
    ' Problem is here
    ' This function is never called :( Even if the query successfully runs
    Me.QryTble.CommandText = Me.OldSql

End Sub

Here is a quick snapshot of the code the creates an instance of this class, finds a relevant QueryTable, then calls Refresh

这是创建此类的实例，找到相关的 QueryTable，然后调用 Refresh 的代码的快速快照

Option Explicit

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

'From MGLOBALS
cacheSheetName = "Cache"
Set cacheSheet = Worksheets(cacheSheetName)

Dim querySheet As Worksheet
Dim interface As Worksheet
Dim classQtEvents As CQtEvents

Set querySheet = Worksheets("QTable")
Set interface = Worksheets("Interface")
Set classQtEvents = New CQtEvents

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 '''
Dim sqlQueryString As String
sqlQueryString = qt.CommandText
Set classQtEvents.QryTble = qt
classQtEvents.OldSql = sqlQueryString ' Cache the original query string


QueryBuilder.BuildSQLQueryStringFromInterface interface, sqlQueryString

' Test message
MsgBox sqlQueryString
qt.CommandText = sqlQueryString

If Not qt Is Nothing Then
    qt.Refresh
Else
    ' ... Error handling code here... 
End If


''' CLEAN UP '''

' Free the dictionary
Set qtDict = Nothing

End Sub

Also here is a screenshot of the Module structure http://imgur.com/8fUcfLV

另外这里是模块结构的截图http://imgur.com/8fUcfLV

My first thought on what might be the issue was passing the QueryTable by value. I am not the most experienced VBA developer, but I reasoned this would create a copy and be calling the event on an unrelated table. However, this was not the case and passing by Reference did not fix the problem either.

我对可能是什么问题的第一个想法是按值传递 QueryTable。我不是最有经验的 VBA 开发人员，但我认为这会创建一个副本并在不相关的表上调用事件。然而，情况并非如此，通过 Reference 传递也没有解决问题。

Also the query is confirmed to run successfully as the data is correctly showing up and being refreshed.

由于数据正确显示和刷新，查询也被确认成功运行。

EDITI added the BeforeRefresh event function to CQtEvents class Module and confirmed this function is called once Refresh is called

编辑我将 BeforeRefresh 事件函数添加到 CQtEvents 类模块并确认调用 Refresh 后调用此函数

Private Sub mQryTble_BeforeRefresh(Cancel As Boolean)
    MsgBox "Start of BeforeRefresh"
End Sub

How might I alter this code get my QueryTable from the QTableModule's RefreshDataQuery() Sub routine to have the AfterRefresh function invoked when the query is successfully ran?

我如何更改此代码从 QTableModule 的 RefreshDataQuery() 子例程获取我的 QueryTable 以在查询成功运行时调用 AfterRefresh 函数？

采纳答案by Kazimierz Jawor

How to catch the AfterRefresh eventof QueryTable?

如何捕捉AfterRefresh eventQueryTable的？

Explanation: in your situation, before event was fired you lost reference of your QueryTableby setting it to nothing when you made cleaning or procedure ended.

解释：在您的情况下，在事件触发之前，您QueryTable在进行清洁或程序结束时将其设置为空，从而失去了对您的引用。

General solution: you must be sure that your code is still running and/or you need to keep any references to your QueryTable.

一般解决方案：您必须确保您的代码仍在运行和/或您需要保留对QueryTable.

1st solution.When calling QT.Refresh methodset the parameter to falsein this way:

第一个解决方案。调用时QT.Refresh method以false这种方式设置参数：

qt.Refresh false 

which will stop further code execution until your qtis refreshed. But I don't consider this solution to be the best one.

这将停止进一步的代码执行，直到您qt刷新。但我不认为这个解决方案是最好的。

2nd solution.Make your classQtEvents variablepublic and after RefreshDataQuery subis finished check the status with some other code.

第二个解决方案。让你的classQtEvents variable公众和后RefreshDataQuery sub完成检查与其他一些代码的状态。

1. in you CQtEvents class moduleadd the following public variable:

   Public Refreshed As Boolean
   

2. in your BeforeRefresh eventadd this:

   Refreshed  = False
   

3. in your AfterRefresh eventadd this line of code:

   Refreshed = True
   

4. Make your classQtEvents variabledeclaration public. Put this before Sub RefreshDataQuery()

   Public classQtEvents as CQtEvents
   

1. 在你CQtEvents class module添加以下公共变量：

   Public Refreshed As Boolean
   

2. 在你BeforeRefresh event添加这个：

   Refreshed  = False
   

3. 在您AfterRefresh event添加这行代码：

   Refreshed = True
   

4. classQtEvents variable公开你的声明。把这个放在前面Sub RefreshDataQuery()

   Public classQtEvents as CQtEvents
   

but remove appropriate declaration from within your sub.

但从您的子程序中删除适当的声明。

Now, even your sub is finished you will be able to check status of refreshment by checking .Refreshed property. You could do it in Immediate or within other Sub. This should work for Immediate:

现在，即使您的 sub 已完成，您也可以通过检查来检查茶点的状态.Refreshed property。您可以在 Immediate 或其他 Sub 中执行此操作。这应该适用于立即：

Debug.Print classQtEvents.Refreshed

3rd solution. (a bit similar to 1st one) Follow steps 1 to 3 from 2nd solution. After you call qt.Refresh methodyou could add this loop which will stop further code execution until qtis refreshed:

第三种解决方案。（有点类似于第一个）按照第二个解决方案的步骤 1 到 3。调用后，qt.Refresh method您可以添加此循环，该循环将停止进一步的代码执行，直到qt刷新：

'your code
If Not qt Is Nothing Then
    qt.Refresh
Else
    ' ... Error handling code here... 
End If
'checking
Do Until classQtEvents.Refreshed
    DoEvents
Loop

Final remark. I hope I didn't mixed up qt variablewith classQtEvents variable. I didn't tried and tested any solution using your variables but wrote all above with referenced to code I use.

最后一句话。我希望我没有qt variable与classQtEvents variable. 我没有使用您的变量尝试和测试任何解决方案，而是参考我使用的代码编写了以上所有内容。

回答by mcw

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

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

As mentioned, if your event handler isn't in scope, or your QueryTable reference is lost, you won't catch the event. The key factors to ensuring you catch the event are:

如前所述，如果您的事件处理程序不在范围内，或者您的 QueryTable 引用丢失，您将无法捕获该事件。确保您赶上事件的关键因素是：

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.)

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