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

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.