Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Query AfterRefresh Event

  1. #1
    Join Date
    11th November 2006
    Posts
    12

    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:

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: AfterRefresh

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

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    Re: AfterRefresh

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

    HTH

    z
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    11th November 2006
    Posts
    12

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    11th November 2006
    Posts
    12

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th November 2006
    Posts
    12

    Re: AfterRefresh

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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    11th November 2006
    Posts
    12

    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:
    VB:
     
    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:

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,924

    Re: AfterRefresh

    JSoto,

    THank you for sharing your solution.

  10. #10
    Join Date
    22nd July 2011
    Posts
    4

    Re: AfterRefresh

    Here You have the solution, explained step by step:

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

    Enjoy!!!!!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 5
    Last Post: July 2nd, 2013, 19:49
  2. Using the AfterRefresh Event
    By socha in forum EXCEL HELP
    Replies: 17
    Last Post: December 27th, 2012, 04:28
  3. Trigger Event Web Query??
    By icene in forum EXCEL HELP
    Replies: 3
    Last Post: March 22nd, 2007, 22:31
  4. Web Query Event
    By johnbest in forum EXCEL HELP
    Replies: 2
    Last Post: March 13th, 2007, 13:06
  5. [Solved] VBA : AfterRefresh Event
    By socha in forum EXCEL HELP
    Replies: 1
    Last Post: April 30th, 2004, 08:07

Bookmarks

Posting Permissions

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