Excel VBA - 刷新完成后未调用 QueryTable AfterRefresh 函数 [英] Excel VBA - QueryTable AfterRefresh function not being called after Refresh completes

问题描述

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

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.

关于如何在类模块中触发此事件函数，我找不到很多不错的资源或文档.在收到对有关 QueryTables 的早期问题的回复后，我决定使用类模块设计路线，而不是将事件处理程序放在工作表中(在这里找到 Excel VBA 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).

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

Here is the code for my Class Module called 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

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

Here is a quick snapshot of the code the creates an instance of this class, finds a relevant QueryTable, then calls 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

这里还有一个模块结构的截图http://imgur.com/8fUcfLV

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

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

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.

此外，由于数据正确显示和刷新，因此确认查询成功运行.

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

编辑我在 CQtEvents 类模块中添加了 BeforeRefresh 事件函数，并确认一旦调用 Refresh 就会调用此函数

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

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

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

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?

推荐答案

如何捕捉QueryTable的AfterRefresh事件?

How to catch the AfterRefresh event of QueryTable?

说明:在您的情况下，在触发事件之前，您会丢失对 QueryTable 的引用，因为在您进行清理或程序结束时将其设置为空.

Explanation: in your situation, before event was fired you lost reference of your QueryTable by 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.

第一种解决方案.在调用QT.Refresh方法时，将参数设置为false，如下:

1st solution. When calling QT.Refresh method set the parameter to false in this way:

qt.Refresh false 

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

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

第二个解决方案.将您的 classQtEvents 变量设为公开，并在 RefreshDataQuery sub 完成后使用其他代码检查状态.

2nd solution. Make your classQtEvents variable public and after RefreshDataQuery sub is finished check the status with some other code.

1. 在您的CQtEvents 类模块中添加以下公共变量:

Public Refreshed As Boolean

在您的 BeforeRefresh 事件 中添加:

Refreshed  = False

在您的 AfterRefresh 事件 中添加以下代码行:

Refreshed = True

公开您的 classQtEvents 变量 声明.把它放在 Sub RefreshDataQuery()

Public classQtEvents as CQtEvents

但从您的子文件中删除适当的声明.

but remove appropriate declaration from within your sub.

现在，即使您的 sub 已完成，您也可以通过检查 .Refreshed 属性 来检查茶点的状态.您可以在 Immediate 或其他 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:

Debug.Print classQtEvents.Refreshed

第三种解决方案.(有点类似于第一个)按照第二个解决方案的步骤 1 到 3.在你调用 qt.Refresh 方法 之后，你可以添加这个循环，它会停止进一步的代码执行，直到 qt 被刷新:

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

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

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

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

这篇关于Excel VBA - 刷新完成后未调用 QueryTable AfterRefresh 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！