Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AfterRefresh event of a QueryTable (XP)

    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!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AfterRefresh event of a QueryTable (XP)

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

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterRefresh event of a QueryTable (XP)

    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.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AfterRefresh event of a QueryTable (XP)

    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

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterRefresh event of a QueryTable (XP)

    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.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AfterRefresh event of a QueryTable (XP)

    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

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterRefresh event of a QueryTable (XP)

    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.<pre>Private Sub CommandButton1_Click()
    Sheets("Detail").QueryTables("LastRunDate").Refres h BackgroundQuery:=False
    End Sub

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

    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...
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AfterRefresh event of a QueryTable (XP)

    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
    <pre>Option Explicit
    Public WithEvents xlQry As QueryTable

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


    In a normal module enter this code:

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

    End Sub
    </pre>


    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

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AfterRefresh event of a QueryTable (XP)

    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! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Cheers!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •