Announcement

Collapse
No announcement yet.

Query AfterRefresh Event

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query AfterRefresh Event



    Hello!

    I looked around to see how to incorporate the query AutoRefresh event to my code with no success. I'm just a beginner with VBA and I don't know how to use the code.

    Here is the link that explains how to use the Query AfterRefresh event... http://support.microsoft.com/kb/213187

    Here is my code:

    Code:
    Public diropen
    
    
    Sub AutoRefresh()
    
    
      Application.DisplayAlerts = False
      Application.ScreenUpdating = True
          
       
        diropen = "C:\Documents and Settings\jsoto\Desktop\"
            
        Workbooks.Open diropen & "Test.xls" _
            , UpdateLinks:=0
       
        
        ActiveWorkbook.RefreshAll
        ActiveWorkbook.Save
    Workbooks("Test.xls").Close
        
       End Sub

    As you can see, it's a simple code to open the workbook, refresh the linked query (from Access), save and close it.

    Can you please help?

    The problem is that the refresh does NOT complete before the save and close and I don't know where to add the AfterRefresh to my code.

    Thanks in advance!

  • #2
    Re: AfterRefresh

    From my reading, you need to create a class module like this:

    Code:
    Public WithEvents qtQueryTable As QueryTable
    
    Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)
        If Success Then
            ActiveWorkbook.Save
            Workbooks("Test.xls").Close
        Else
            ' Do whatever you should if things went south
        End If
    End Sub
    ... and delete the last two lines of code in your other module.
    Entia non sunt multiplicanda sine necessitate.

    Comment


    • #3
      Re: AfterRefresh

      I've attached a spreadsheet with the basics to get you started

      HTH

      z
      Attached Files

      Comment


      • #4
        Re: AfterRefresh

        Thank you both!

        shg, for some reason is not doing the save and close. I tested it adding the code to the Class Module in Test.xls and that didn't work. I also added it to the book1.xls, where I'm running the macro, and didn't work either.

        What am I doing wrong?

        Thanks!

        Comment


        • #5
          Re: AfterRefresh

          10. On the Tools menu, point to Macro, and then click Macros. In the Macro dialog box, click Initialize_It and then click Run.
          11. On the Data menu, click Refresh Data.

          Comment


          • #6
            Re: AfterRefresh

            Thank you zimitry!

            I have over 50 workbooks with queries to refresh every month and what I'm trying to avoid is to go and refresh them one by one.

            The example code from MS support works but I don't know how to adapt it to my scenario...

            I want the macro to open the workbook, refresh the query and close the wb again.

            Thank you again.

            Comment


            • #7
              Re: AfterRefresh

              Can someone help me with this code? I will appreciate it!

              Comment


              • #8
                Re: AfterRefresh

                Guys,

                Here is the solution to my problem in case you need it.

                I finally figured it out!

                This code goes on a Module:
                Code:
                Dim X As New Class1 
                
                Sub Initialize_It() 
                
                    Application.DisplayAlerts = False 
                    Application.ScreenUpdating = True 
                      
                      
                    diropen = "C:\Documents and Settings\jsoto\Desktop\" 
                      
                    Workbooks.Open diropen & "Test.xls" _ 
                    , UpdateLinks:=0 
                    
                  Set X.qt = Workbooks("Test.xls").Sheets("Sheet1").QueryTables(1) 
                          
                   ActiveWorkbook.RefreshAll 
                
                      
                End Sub
                This code goes on a Class Module:

                Code:
                 
                
                Public WithEvents qt As QueryTable 
                
                Private Sub qt_AfterRefresh(ByVal Success As Boolean) 
                
                   ' Declare variables. 
                   Dim a As Integer 
                   Dim My_Prompt As String 
                
                   ' Initialize prompt text for message box. 
                   My_Prompt = "Data refreshed." 
                
                   ' Get YES or NO result from the message box 
                    'a = MsgBox("Do you want to refresh the data now?", vbYesNo) 
                
                   ' Check to see whether YES or NO was selected. 
                   If a = vbNo Then 
                
                      ' Change prompt text for message box. 
                      My_Prompt = "Data will not be refreshed." 
                
                      ' Cancels the Query Refresh. 
                      Cancel = True 
                
                   End If 
                
                   ' Displays message box before refresh (or non-refresh) occurs. 
                   MsgBox My_Prompt 
                    
                   ActiveWorkbook.Save 
                   Workbooks("Test.xls").Close 
                
                End Sub

                Have fun!!

                Comment


                • #9
                  Re: AfterRefresh

                  JSoto,

                  THank you for sharing your solution.
                  Reafidy

                  Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                  Comment


                  • #10


                    Re: AfterRefresh

                    Here You have the solution, explained step by step:

                    http://vbaproexcel.blogspot.com/2011...sh-method.html

                    Enjoy!!!!!

                    Comment

                    Working...
                    X