Excel VBA QueryTable callback function after table refresh

Question

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?

Answer 1

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.