Excel VBA - QueryTable AfterRefresh function not being called after Refresh completes

Question

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 https://i.stack.imgur.com/snZgI.jpg

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?

but there isn't `MsgBox` within your `AfterRefresh` event Sub. So, how do you know if it's finished/refreshed? I can't see the code is checking for the status of that event. You could add a `MsgBox` to the event to check how (if) it's working. — Kazimierz Jawor, Aug 08 '13 at 21:27
@KazJaw I put a MsgBox in the event function and confirmed it is not being called. Also, another way I can confirm is viewing the QueryTable's query to verify that the original has been restored, which is what I was doing instead of using a MsgBox. — Paul Renton, Aug 08 '13 at 21:30
keep the message box within `AfterRefresh event` and call refresh in you main sub in this way: `qt.Refresh false`. Do you get MsgBox now? — Kazimierz Jawor, Aug 08 '13 at 21:33
@KazJaw Yes that did trigger the AfterRefresh to run.. Interesting — Paul Renton, Aug 08 '13 at 21:36
do you know the reason and do you know what to do next? there are...three possible solution which help you catch the event... Will you solve it on your own or you need any support? — Kazimierz Jawor, Aug 08 '13 at 21:37
@KazJaw reading the following doc http://msdn.microsoft.com/en-us/library/office/ff839478.aspx It looks like if the optional boolean isn't supplied, the BackgroundQuery property dictates what occurs. I have it working now but would like to know why it works if possible. Would you mind posting an answer explaining potential solutions so it can help myself and others? — Paul Renton, Aug 08 '13 at 21:44

score 4 · Accepted Answer · answered Aug 08 '13 at 22:09

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.

in you CQtEvents class module add the following public variable:
```
Public Refreshed As Boolean
```
in your BeforeRefresh event add this:
```
Refreshed  = False
```
in your AfterRefresh event add this line of code:
```
Refreshed = True
```
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.

Thank you this is very helpful. So in summary, because my procedure had finished executing before the query finished executing, I lost all references to the QueryTable which lead to no AfterRefresh callback? — Paul Renton, Aug 08 '13 at 22:20
yes, that is exactly what I expect (even I can't see complete of your code) — Kazimierz Jawor, Aug 08 '13 at 22:21

score 1 · Answer 2 · edited May 23 '17 at 12:14

A github repo that demonstrates the minimum code needed to get this working can be found here.

As mentioned, if your event handler isn't in scope, or your QueryTable reference is lost, you won't catch the event. The key factors to ensuring you catch the event are:

Declare a global variable of your event-handling class module's type outside of any subroutines/methods, at the top of a file (I chose the ThisWorkbook file).
Add a Workbook_Open event handler and instantiate that variable there, so that it is available immediately and will remain in scope (since it's global).
At that point, or at any downstream point when you have a QueryTable you're interested in, pass that QueryTable to the global instance to wire up its events.

(It took me a couple tries to figure this out myself, when someone pointed me in this direction as an answer to this question.)

Excel VBA - QueryTable AfterRefresh function not being called after Refresh completes

2 Answers2

Linked

Related