C语言querytable函数,Excel VBA – 在Refresh完成后未调用QueryTable AfterRefresh函数

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

我无法找到许多有关如何在类模块中触发此事件功能的合适资源或文档.我决定使用类模块设计路线,而不是在收到对先前关于QueryTables的问题的回复(在此处找到Excel VBA AfterRefresh)后将事件处理程序放在工作表中.

这是我的类模块的代码,称为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

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

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

此外,当数据正确显示并刷新时,确认查询成功运行.

编辑

我将BeforeRefresh事件函数添加到CQtEvents类Module并确认一旦调用Refresh就调用此函数

Private Sub mQryTble_BeforeRefresh(Cancel As Boolean)

MsgBox "Start of BeforeRefresh"

End Sub

我如何改变这段代码从QTableModule的RefreshDataQuery()Sub例程获取我的QueryTable,以便在成功运行查询时调用AfterRefresh函数？