Query AfterRefresh Event

[JSoto]

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:

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!

[shg]

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

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.

[Craig Ottley]

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

HTH

z

[JSoto]

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!

[Craig Ottley]

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.

[JSoto]

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.

[JSoto]

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

[JSoto]

Guys,

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

I finally figured it out!

This code goes on a Module:

 
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:

 
 
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!!

[Reafidy]

JSoto,

THank you for sharing your solution.

[alossondro]

Here You have the solution, explained step by step:

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

Enjoy!!!!!