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

问题描述

我正在使用VBA开发一个Excel（2010+）应用程序，并遇到一个问题，一旦查询完成执行，没有调用AfterRefresh事件函数。

我未能找到许多体面的资源或文档，了解如何在类模块中触发此事件功能。我决定使用类模块设计路线，而不是在接收到关于QueryTables的早期问题的响应之后将事件处理程序放在工作表中（在这里发现 Excel VBA AfterRefresh ）。

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

  Option Explicit 
 
 Private WithEvents mQryTble As Excel.QueryTable 
私人msOldSql As String 
 
'属性
公共属性集QryTble（ByVal QryTable As QueryTable）：设置mQryTble = QryTable：
结束属性
公共属性获取QryTble（）作为QueryTable：Set QryTble = mCryTble：
结束属性
公共属性让OldSql（ByVal sOldSql As String）：msOldSql = sOldSql：
结束属性
公共属性获取OldSql（）As String：OldSql = msOldSql： 
结束属性
 
 Private Sub Class_Initialize（）
 MsgBoxCQtEven ts init
 End Sub 
 
'将查询sql重置为原始未修改的sql语句
'当刷新线程完成执行
时调用此方法Private Sub mQryTble_AfterRefresh （ByVal Success As Boolean）
'问题在这里
'这个函数从来没有被调用:(即使查询成功运行
 Me.QryTble.CommandText = Me.OldSql 
 
 End Sub 
  

以下是创建此类实例的代码的快照，找到相关的QueryTable，然后调用刷新

  Option Explicit 
 
 Sub RefreshDataQuery（）
 '依赖关系：字典（HashTable）对象的Microsoft脚本运行时（工具 - >引用）
 
'从MGLOBALS 
 cacheSheetName =Cache
设置cacheSheet = Worksheets（cacheSheetName） 
 
 Dim querySheet As Worksheet 
 Dim interface As Worksheet 
 Dim classQtEvents As CQtEvents 
 
设置querySheet = Wor ksheets（QTable）
设置接口=工作表（接口）
设置classQtEvents =新的CQtEvents 
 
 Dim qt As QueryTable 
 Dim qtDict As New Scripting。字典
 
设置qtDict = UtilFunctions.CollectAllQueryTablesToDict 
设置qt = qtDict.Item（从fred2查询
 
'''构建SQL查询字符串''' 
 Dim sqlQueryString As String 
 sqlQueryString = qt.CommandText 
设置classQtEvents.QryTble = qt 
 classQtEvents.OldSql = sqlQueryString'缓存原始查询字符串
 
 
 QueryBuilder.BuildSQLQueryStringFromInterface接口，sqlQueryString 
 
'测试消息
 MsgBox sqlQueryString 
 qt.CommandText = sqlQueryString 
 
如果不是qt是没有
 qt.Refresh 
 Else 
'...错误处理代码在这里... 
结束如果
 
 
'''CLEAN UP '''
 
'免费字典
设置qtDict = Nothing 
 
 End Sub 
  

A以下是模块结构的屏幕截图 http://imgur.com/8fUcfLV

我第一次想到可能是什么问题是通过值传递QueryTable。我不是最有经验的VBA开发人员，但是我推测这将创建一个副本，并在不相关的表上调用该事件。但是，事实并非如此，通过引用也没有解决问题。

此外，查询被确认成功运行，因为数据正确显示并刷新。

EDIT
我向CQtEvents类Module添加了BeforeRefresh事件功能，并确认此功能被调用一次Refresh被调用

  Private Sub mQryTble_BeforeRefresh（取消为布尔值）
 MsgBoxBeforeRefresh开始
 End Sub 
  

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

解决方案

如何捕获QueryTable的 AfterRefresh事件？

说明：在您的情况下，在事件触发之前，当您进行清理或过程结束时，将$ QueryTable 的引用设置为空。

常规解决方案：您必须确保您的代码仍在运行，和/或您需要保留对 QueryTable 。

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

  qt.Refresh false 
  

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

第二个解决方案。使你的 classQtEvents变量 public，之后 RefreshDataQuery sub 已完成使用其他代码检查状态。

1. 您 CQtEvents类模块添加以下公共变量：

    公开刷新为布尔
     




2. 在您的 BeforeRefresh事件添加：

     Refreshed = False 
    
    




3. 在您的 AfterRefresh事件中添加以下代码行：

     Refreshed = True 
     




4. 将您的 classQtEvents变量声明为public。把这个在 Sub RefreshDataQuery（）

     public classQtEvents as CQtEvents 
     

，但从您的子文件中删除适当的声明。 p>

现在，即使你的子程序完成，你也可以通过检查 .Refreshed属性来检查刷新状态。你可以在立即或其他Sub中进行。这应该适用于立即：

  Debug.Print classQtEvents.Refreshed 
  

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

 '你的代码
如果不是qt是没有，然后
 qt.Refresh 
 Else 
'...错误处理代码在这里... 
结束如果
'检查
做直到classQtEvents.Refreshed 
 DoEvents 
循环
  

最终评论。我希望我没有将 qt变量与 classQtEvents变量混合起来。我没有尝试使用你的变量来测试任何解决方案，但写了所有上面引用我使用的代码。

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.

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

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

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

Also here is a screenshot of the Module structure 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.

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

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

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?

解决方案

How to catch the AfterRefresh event of 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.

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

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

qt.Refresh false 

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

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

1. in you CQtEvents class module add the following public variable:

   Public Refreshed As Boolean
   

2. in your BeforeRefresh event add this:

   Refreshed  = False
   

3. in your AfterRefresh event add this line of code:

   Refreshed = True
   

4. Make your classQtEvents variable declaration public. Put this before 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:

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

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屋！