Knowledge Base

XL97: How to Use the Query Before and AfterRefresh Events

Article ID: 182735

Article Last Modified on 11/23/2006

APPLIES TO

This article was previously published under Q182735

SUMMARY

In Microsoft Excel, you can automatically run a macro before or after updating (refreshing) worksheet query data. You can do this with the BeforeRefresh and AfterRefresh Visual Basic for Applications events. This article contains an example of how to use the two events to run a macro.

MORE INFORMATION

Before you can use the sample macro, retrieve data from Microsoft Query to a Microsoft Excel worksheet. To create the sample data, follow these steps:

  1. On the Data menu, point to Get External Data, and then click Create New Query.
  2. In the Choose Data Source dialog box, click <New Data Source>, clear the "Use the Query Wizard to create/edit queries" check box, and then click OK.
  3. In the Create New Data Source dialog box, type test in the first box.
  4. Click the drop-down for the second combo box, and then click Microsoft dBase Driver (*.dbf).
  5. Click Connect.
  6. In the ODBC dBase Setup dialog box, clear the Use Current Directory check box, and then click Select Directory.
  7. In the Select Directory dialog box, locate the following folder:

    Program Files\Microsoft Office\Office

    The Customer.dbf, Employee.dbf, and Orders.dbf files should be listed in this folder. If they are not, run the Office Setup program and install them.
  8. Click OK.
  9. Click OK in the ODBC dBase Setup dialog box.
  10. Click OK in the Create New Data Source dialog box.
  11. Make sure the "test" data source is selected in the Choose Data Source dialog box, and then click OK.

    Microsoft Query is started and the Add Tables dialog box is displayed.
  12. Click Customer.dbf, and then click Add.
  13. Click Close.
  14. Double-click the * listed in the Customer table.

    This step adds all the fields to the result set in the Data pane.
  15. On the File menu, click Return Data to Microsoft Excel.
This creates the query table to use with the sample macro.

Programming Example

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

In this example you set up a Visual Basic for Applications macro that responds to the BeforeRefresh or AfterRefresh event in a worksheet. To set up the event handler, follow these steps:

  1. Switch to the worksheet that contains the data that is returned from Microsoft Query.
  2. Start the Visual Basic Editor (press ALT+F11).
  3. If the Project Explorer window is not visible, click Project Explorer on the View menu.
  4. On the Insert menu, click Class Module.
  5. Click in the code window for the Class Module and enter the following code: 
          Public WithEvents qt As QueryTable

      Private Sub qt_BeforeRefresh(Cancel As Boolean)

        ' Declare variables.

        Dim a As Integer
        Dim My_Prompt As String

         ' Initialize prompt text for message box.
         My_Prompt = "Data will be refreshed."

          ' Get YES or NO result from the message box
          a = MsgBox("Do you want to refresh the data now?", vbYesNoCancel)

          ' Check to see wheather 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

      End Sub
    NOTE: To use the AfterRefresh event you can replace the macro name "qt_BeforeRefresh(Cancel As Boolean)" with qt_AfterRefresh(ByVal Success As Boolean) in the above example. Also, you should change the message box text to an appropriate post Data Refresh message.
  6. On the Insert menu, click Module.
  7. Click in the Code window for the Module and enter the following code: 
                Dim X As New Class1

      Sub Initialize_It()
        Set X.qt = Thisworkbook.Sheets(1).QueryTables(1)
      End Sub
  8. Switch to Microsoft Excel (ALT+F11).
  9. Click any cell in the worksheet query data.
  10. On the Tools menu, point to Macro, click Macros, and then run the Initialize_It macro.
  11. On the Data menu, click Refresh Data.
The BeforeRefresh event should now run before the actual query data is refreshed. If you used the AfterRefresh event, the event runs after the actual query data has been refreshed.

NOTE: For the BeforeRefresh and AfterRefresh events to work, the Initialize_It macro must be run anytime the workbook is opened. You may want to use the name Auto_Open rather than Initialize_It. This will allow the two events to automatically function as expected when you refresh the query table data.

REFERENCES

For more information about using event handlers, click the Office Assistant, type events, click Search, and then click to view the "Using events with Microsoft Excel objects" topic.

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Excel Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

120802 Office: How to Add/Remove a Single Office Program or Component


Additional query words: XL97 Event Query

Keywords: kbhowto KB182735


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.