Using the AfterRefresh Event

  • I have some VBA code which does a special paste and sort which I want to do every time I refresh a certain query. I am having little luck working through the example on the VBA help menu about how to use the Query Table events. From the description I could not tell where to put the code, and where to plug in my own subroutine into the example. Here is what I tried.


    I created a Class Module and called it ClsModQT. Then tried to "connect the declared object in the class module to the object. Following the example, here is what I put into that module:

    Code
    Public WithEvents qtQueryTable As QueryTable
    Sub InitQueryEvent(QT As Object)
    Set qtQueryTable = QT
    End Sub


    Code
    Dim clsQueryTable As New ClsModQT
    Sub RunInitEvent()
    clsQueryTable.InitQueryEvent QT:=ActiveSheet.QueryTables(1)
    End Sub


    (Then my own subroutine)

    Code
    Private Sub qtQueryTable_AfterRefresh(Cancel As Boolean)
    sortAlumPage
    End Sub


    Nothing happens at all. This subroutine works fine when I run it myself.


    What am I doing wrong?

  • Yes, Derk, the subroutine is in the module for the sheet with the query (which is also where my result will be). The AfterRefresh event would get stored in a new class module as far as I can tell.


    For the refresh, right now I am just trying it with the query refresh button. After I get my whole project in order, I will change the query properties to refresh when I open the workbook.


    I hope someone can answer this for both of us. This AfterRefresh event appears to be a basically simple procedure (as if anything is simple to a novice like me), but the help instructions have me stumped.

  • Socha
    OK ODBC ActiveSheet.QueryTables(1)


    I would not choose to refresh in VBA its not so good I fine. I use Microsoft Query ODBC everyday and choose to add table after I delete all the cells contents and destination to cell A1


    All the bits you talk about I would add to just one routine, not have all the bits and pieces as you have – im not telling you what to do, more explaining in my opinion what I feel is best and explaining how I work


    Might be good to record and download in the VBE and look at the code and your see that you can do a lot more than at first it looks like.


    I will see if I have an old VB Script here at home and post up so you can see for yourself what I am getting at



    Kindest possible regards



    Jack in the UK

  • Good news i have found a very old code which will not work at your end and is only an example to look over you will see the ADD.


    Kindest possible regards


    Jack in the UK



  • Great idea Derk, why didn't I think of that? I'm pretty much set then. But I'll take a look at Jack in the U.K.s code too and see if I can do it that way too, it would be a handy thing to know for future projects that don't call for the refresh on opening.

  • I'm sorry, I'm a bit scattered these days and have some other priorities that came up. I haven't been able to work on it, but hope to resume the project in a day or so. I'll post what I did, and whether it was successfuly.
    By the way, I notice that some posts are posted with the word SOLVED. Does the administrated mark it that way, or is there something I do to post it as solved?

  • Thanks for the feed back I look forward to Your reposts, funny I learn s much from this kind of thing, yup sounds silly I know, but anyone knows im very open and honest thus my previous posts on the subject


    Good stuff Your very busy, hope few hours a day nose in Microsoft Excel !!!


    Can I ask You kindly to keep all posts re this question on the same feed just add them on the bottom via reply!


    Hey [SOLVED]


    Yes there was a sassy button that was Excel cool, the site owner and close buddy of mine Mr Dave Hawley knackered it so its lost until we fix it LOL !!


    What I suggest is when you say its solved, just add to the first post you made and edit the title so this addition is there. But of fun to.


    Not solved yet thou!!!

  • Using the Microsoft Knowledge Base article I followed the example for a BeforeRefresh and an AfterRefresh (for each one it took a Class Module & a regular Module). It did take one additional module to use the Open workbook event to run the initialize subs.


    Using the sample code in the article, I plugged in the name of my worksheet where it says Thisworkbook.Sheets(1). I added the AfterRefresh sub and put a message that says "Data has been refreshed." Now I can plug into that sub any code I want to.


    Here's my code:


    (The subs for initializing are in the regular Modules)


    Code
    Dim X As New Class1
    Sub Initialize_It()
    Set X.qt = ThisWorkbook.Sheets("Alum").QueryTables(1)
    End Sub
    
    
    Dim X As New Class2
    Sub Initialize_It()
    Set X.qt = ThisWorkbook.Sheets("Alum").QueryTables(1)
    End Sub


    And one Workbooks event sub to run the initialization with an Open workbook event:

    Code
    Private Sub Workbook_Open()
    Module10.Initialize_It
    Module11.Initialize_It
    MsgBox "Alum worksheet has been initialized"
    End Sub


    (module 10 & module 11 refer to the locations of the subs that initialize the BeforeRefresh and AfterRefresh).


    And so this is a complete success. I think I'll get a lot of use out of this one. I hope it helps somebody else too.


    Thanks everybody for the help and encouragement.

  • Re: Using the AfterRefresh Event SOLVED


    Hello to all ozgrid forum users,
    I tried to use this old thread about AfterRefresh event but due to my “proficiency” in VBA got stuck. I want to put date and time stamp upon successful data refresh from web queries that are set as data sources in the excel file. Refresh is set on excel file open. I tried to reuse some parts of socha code but miserably failed. Here how it looks like:
    Class1:


    Module1:

    Code
    Dim X As New Class1
    Sub InitializeIt()
        Set X.qt = ThisWorkbook.Sheets("CR").QueryTables(1)
    End Sub


    ThisWorkbook:

    Code
    Private Sub InitializeIt_Open()
        Module1.InitializeIt
        MsgBox "QT worksheet has been initialized"
    End Sub


    It seems that sub for initialization couldn’t be initialized, but that is where my knowledge ends. There is RT error No.9 in Module1, row with Set X.qt function.
    Basically macro has to check if data in sheet CR is refreshed successfully and to put the status, date and time on specified cells in sheet Status.
    Do somebody have some idea how to solve this?

  • Re: Using the AfterRefresh Event SOLVED


    It's probably better to start your own thread, linking to this one, but I'll help you here.


    Quote from BoVBA;641709


    Module1:

    Code
    Dim X As New Class1
    Sub InitializeIt()
        Set X.qt = ThisWorkbook.Sheets("CR").QueryTables(1)
    End Sub

    It seems that sub for initialization couldn’t be initialized, but that is where my knowledge ends. There is RT error No.9 in Module1, row with Set X.qt function.

    Run-time error 9, Subscript out of range. Always give the full exact error message. The error occurs either because there isn't a sheet called CR or there isn't web query on the CR sheet. In fact, your MsgBox prompt refers to a QT worksheet, so the former reason probably applies, therefore try changing "CR" above to "QT".


    Quote from BoVBA;641709


    ThisWorkbook:

    Code
    Private Sub InitializeIt_Open()
        Module1.InitializeIt
        MsgBox "QT worksheet has been initialized"
    End Sub

    That should be:

    Code
    Private Sub Workbook_Open()
        Module1.InitializeIt
        MsgBox "QT worksheet has been initialized"
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!