表刷新后的Excel VBA QueryTable回调函数 [英] Excel VBA QueryTable callback function after table refresh

问题描述

我正在编写/维护一个Excel VBA应用程序，其中有多个QueryTables链接到MS SQL Server数据库。应用程序的用户可以通过操作Excel文档上的各种UI控件来将SQL查询更改为每个表。

我遇到的QueryTables之一是使用多线程。文档上的每个QueryTable具有在运行查询后必须还原的原始状态。例如，如果QueryTable1具有基本查询

 从example_table 
  / pre> 
 
 
，用户选择控件上的某些输入来创建
 从example_table选择*其中object_oid ='10'
  
我需要恢复原始状态。下面的代码是我目前正在完成这个
  Sub RefreshDataQuery（）
'依赖关系：Microsoft Scripting Dictionary（HashTable）对象的运行时（Tools->引用）
 
 Dim querySheet As Worksheet 
 Dim interface As Worksheet 
 
设置querySheet = Worksheets（QTable ）
 Set interface = Worksheets（Interface）
 
 Dim sh As Worksheet 
 Dim qt As QueryTable 
 Dim qtDict As New Scripting.Dictionary 
 
设置qtDict = UtilFunctions.CollectAllQueryTablesToDict 
 
设置qt = qtDict.Item（从fred2查询
 
'''构建SQL查询字符串'''
 Dim sqlQueryString As String 
 Dim originalQueryCache As String 
 originalQueryCache = qt.CommandText 
 sqlQueryString = qt.CommandText 
 
 QueryBuilder.BuildSQLQueryStringFromInterface接口，sqlQueryString 
 
 MsgBox sqlQueryString 
 
 qt.CommandText = sqlQueryString 
 
如果不是qt是没有了
 qt.Refresh 
 Else 
'错误消息和处理这里
'切出来保持代码短
结束如果
 
 
'''CLEAN UP'''
 
'恢复原始基本SQL查询
'问题在这里
'这个或任何其他改变的语句会出错如果查询仍然刷新
 qt.CommandText = originalQueryCache 
'其他原始状态恢复代码如下... 
 
'免费字典
设置qtDict = Nothing 
 
 
 End Sub 
  
理想情况下，如果我在写这个另一种现代语言，我将创建一个回调函数或运行刷新在我自己的线程与完成通知程序。我花了很多时间研究如何获得qt.Refresh调用的回调函数，但没有运气。我意识到我可以黑客一下，但是我宁愿不从事坏的做法，因为许多人将来必须保持这一点。 
 
 
 
此应用程序必须支持Excel 2010版本和向上
 
 
 
那么如何为VBA函数创建一个回调函数这是在单独的线程运行？或者，我应该看另一种方法吗？
解决方案
 QueryTables事件不会被公开，除了通过自定义类模块和WithEvents关键字。首先，创建一个名为CQtEvents的自定义类模块，并将其放在其中：
  Private WithEvents mQryTble As QueryTable 
 Private msOldSql作为String 
 
公共属性集QryTble（ByVal QryTble As QueryTable）：设置mQryTble = QryTble：End Property 
公共属性获取QryTble（）作为QueryTable：Set QryTble = mQryTble：End Property 
公共属性让OldSql（ByVal sOldSql As String）：msOldSql = sOldSql：End Property 
公共属性获取OldSql（）As String：OldSql = msOldSql：End Property 
 
 Private Sub mQryTble_AfterRefresh ByVal Success As Boolean）
 
 Me.QryTble.CommandText = Me.OldSql 
 
 End Sub 
  
这是两个属性：一个用于保存QueryTable，一个用于存储旧的sql。那么你的程序会像
  Sub RefreshDataQuery（）
 
 Dim interface As Worksheet 
 Dim qt As QueryTable 
 Dim qtDict As New Scripting.Dictionary 
 Dim clsQtEvents As CQtEvents 
 Dim sqlQueryString As String 
 
设置qtDict = UtilFunctions.CollectAllQueryTablesToDict 
设置qt = qtDict.Item（从fred2查询
 
 sqlQueryString = qt.CommandText 
 QueryBuilder.BuildSQLQueryStringFromInterface接口，sqlQueryString 
 
'为事件创建类，存储旧的sql 
设置clsQtEvents =新的CQtEvents 
设置clsQtEvents.QryTble = qt 
 clsQtEvents.OldSql = qt.CommandText 
 
 qt.CommandText = sqlQueryString 
 
如果不是qt是没有，然后
 qt.Refresh完成后，类中的事件将触发
 Else 
'错误消息和处理这里
结束如果
 
 End Sub 
  
由于您使用WithEvents定义mQryTble，所以它在事件（BeforeRefresh和AfterRefresh）中都会显示在类中。通过将CQtEvents.QryTble设置为您的QueryTable，该类然后监听该QueryTable上的事件。在更改之前，CommandText存储在OldSql中。然后当刷新完成时，事件触发并恢复CommandText。当然不是刷新是在事件中完成的，但我假设你想要旧的sql语句，如果它被刷新或重新处理。
 
 
 
接下来，你应该考虑做一个集合类来保存一堆QtEvents实例。我以你的代码处理为例，但你真的在做更多的事情。然后，您可以将CollectAllQueryTables移动到该集合类中，并将BuildSQL部分移动到CQtEvents类中。
 
I am writing/maintaining an Excel VBA application where there are multiple QueryTables linked to MS sql server databases. Users of the application can alter the SQL query to each table by manipulating various UI controls on the Excel document. 

One of the issues I have come across with QueryTables is there use of multi threading. Each QueryTable on the document has an original state that must be restored after a query is ran. For instance, if QueryTable1 had a base query of
Select * from example_table
and the user selected certain inputs on the controls to create
Select * from example_table Where object_oid = '10'
I would need the original state to be restored. The code below is a snapshot of how I am currently accomplishing this
Sub RefreshDataQuery()
'Dependencies: Microsoft Scripting Runtime (Tools->References) for Dictionary (HashTable) object

Dim querySheet As Worksheet
Dim interface As Worksheet

Set querySheet = Worksheets("QTable")
Set interface = Worksheets("Interface")

Dim sh As Worksheet
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
Dim originalQueryCache As String
originalQueryCache = qt.CommandText
sqlQueryString = qt.CommandText

QueryBuilder.BuildSQLQueryStringFromInterface interface, sqlQueryString

MsgBox sqlQueryString

qt.CommandText = sqlQueryString

If Not qt Is Nothing Then
    qt.Refresh
Else
    'Error Messages and handling here
    ' Cut out to keep code short
End If


''' CLEAN UP '''

'Restore the original base SQL query
' Problem is here
' This, or any other altering statement, will error out if the query is still refreshing
qt.CommandText = originalQueryCache
' Other original state restoring code below...

' Free the dictionary
Set qtDict = Nothing


End Sub
Ideally, if I was writing this in another modern language, I would create a callback function or run the refresh in my own thread with a completion notifier. I spent a good chunk of time researching how to get a callback function for the qt.Refresh call, but am having no luck. I realize I could 'hack' around this a bit but I would prefer to not engage in bad practices as many people will have to maintain this in the future. 

This application must support Excel 2010 versions and upward

So how can I create a callback function for VBA functions that are run in separate threads? Or, should I be looking at another approach?
解决方案
The QueryTables events aren't exposed except through a custom class module and the WithEvents keyword.  First, create a custom class module named CQtEvents and put this in it:
Private WithEvents mQryTble As QueryTable
Private msOldSql As String

Public Property Set QryTble(ByVal QryTble As QueryTable): Set mQryTble = QryTble: 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 mQryTble_AfterRefresh(ByVal Success As Boolean)

    Me.QryTble.CommandText = Me.OldSql

End Sub
That's two properties: one to hold the QueryTable and one to store the old sql. Then your procedure would look something like
Sub RefreshDataQuery()

    Dim interface As Worksheet
    Dim qt As QueryTable
    Dim qtDict As New Scripting.Dictionary
    Dim clsQtEvents As CQtEvents
    Dim sqlQueryString As String

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

    sqlQueryString = qt.CommandText
    QueryBuilder.BuildSQLQueryStringFromInterface interface, sqlQueryString

    'Create class for events and store old sql
    Set clsQtEvents = New CQtEvents
    Set clsQtEvents.QryTble = qt
    clsQtEvents.OldSql = qt.CommandText

    qt.CommandText = sqlQueryString

    If Not qt Is Nothing Then
        qt.Refresh 'after this is done, the event in the class will fire
    Else
        'Error Messages and handling here
    End If

End Sub
Because you define mQryTble with WithEvents, its two events (BeforeRefresh and AfterRefresh) are exposed in the class. By setting CQtEvents.QryTble to your QueryTable, the class then listens for events on that QueryTable. The CommandText is stored in OldSql before it's changed. Then when the Refresh is done, the event fires and the CommandText is restored. Of course not Refresh is done in the event, but I assume you want the old sql statement in there if it's refreshed or reprocessed.

Next, you should consider making a collection class to hold a bunch of QtEvents instances. I presume your code processes one as an example, but you're really doing more. Then you can move your CollectAllQueryTables inside that collection class and move the BuildSQL part inside the CQtEvents class.

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