AfterRefresh event of a QueryTable (XP)

Topic

New Reply

Can somebody provide me with a working example of using the AfterRefresh event of a QueryTable, or a link to something more intelligible than what MS deigns to provide in the Help file? Thanks in advance!

Reply | Quote

Replies

Could you be a little more specific about you you are trying to do? Your question is pretty vague.

Steve

Reply | Quote

Sure. I have a worksheet with two seperate QueryTables. After both have refreshed, running queries against an Access mdb, I’d like to compare two cells, one from each query table. My code, as it stands in a single Sub, calls the ActiveWorkbook.RefreshAll method and then starts to compare the cells. The problem is that the comparison runs well before the RefreshAll finishes and so yields erroneous results.

So, the AfterRefresh event springs to mind as a good event to trap and use to compare the cells. However, the MS team is as helpful as ever in the Help file and I’m having a bit of trouble following the example they give on how to use the AfterRefresh event of a QueryTable.

Reply | Quote

Instead of having 1 routine: use 2 routines:

Have one routine before the refresh and this starts the “refreshall”

Have another routine, which is the task after it is refreshed. The 2nd routine is called by the “afterrefresh” event.

Routine 1 runs and calls for the refresh and this routine ends!
After the refresh is done, the afterrefresh event is triggered and this calls the 2nd routine (to compare)
the 2nd routine runs and finishes.

Steve

Reply | Quote

Right. Now if you can help me make sense of the example provided by MS on how to actually use the AfterRefresh event of a QueryTable, we’d be on to something. Check the Help topic “Using Events with the QueryTable Object” to see the source of my confusion.

Reply | Quote

Assume you have: 2 subs (Mod-Before and Mod-After) and 2 queryTables (named querytable1 and querytable2)
This runs mod-before which refreshes QT1. After QT1 is refreshed, it refreshes QT2. After QT2 is refreshed, it calls mod-after.

I thought about using refreshall, but wanted to be sure BOTH were refreshed before calling mod-after so had them run in series.

Steve

Sub Mod-before()
‘this does all you want BEFORE refresh
‘blah
‘blah
Querytable1.refresh
end sub

Sub Mod-After()
‘this does all you want AFTER refresh
‘blah
‘blah
end sub

Private Sub QueryTable1_AfterRefresh(Success As Boolean)
querytable2.refresh
End Sub

Private Sub QueryTable2_AfterRefresh(Success As Boolean)
mod-after
End Sub

Reply | Quote

If you can get that to work, Steve, you’re a better man than I. The below code doesn’t fire the AfterRefresh event for the QueryTable named LastRunDate.

Private Sub CommandButton1_Click()
    Sheets("Detail").QueryTables("LastRunDate").Refresh BackgroundQuery:=False
End Sub

Private Sub LastRunDate_AfterRefresh(Success As Boolean)
    MsgBox "Oh...I updated!"
End Sub

All of Microsoft’s literature suggests that you first must code a seperate class module to declare a QueryTable object, then dance the Hokey-Pokey to get the nasty @#$!% to work. Has anybody, anywhere in the history of clever developers actually used the AfterRefresh event of a QueryTable? I’d trade a multitude of refreshments to see one working example pulling data from the Northwind mdb…

Reply | Quote

I apologize. It was more complicated. I couldn’t test it at work since I have no drivers to import data and I don’t have the “authority” to add new ones: Only administrators can, so I had to test it at home and I understand your frustration.

In VB (alt-f11)
Insert Class Module
Name it via properties – (F4) “clsQry” (no quotes)
Add this code to the class module

Option Explicit
Public WithEvents xlQry As QueryTable

Private Sub xlQry_AfterRefresh(ByVal Success As Boolean)
    MsgBox ("This is after refresh")
End Sub

In a normal module enter this code:

Option Explicit
Dim x As New clsQry
Sub Tester()
    Set x.xlQry = ActiveSheet.QueryTables(1)
    x.xlQry.Refresh
    
End Sub

Run “Tester” macro with the “querytable on the active sheet and it will refresh the query and give the test message from the afterrefresh event.

Steve

Reply | Quote

Eureka!! Thanks, Steve, for staying with me! It makes good sense to me now, in practice if not in understanding why MS chose to implement the functionality that way, and has been put to good use. If you find yourself in Minneapolis, the pints are on me! Cheers!

Reply | Quote