XL97: How to Use the Query Before and AfterRefresh Events

• Microsoft Excel 97 for Windows

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.

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 engineers 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 the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

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.

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" (without quotation marks), 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:

   ARTICLE-ID: Q120802
   TITLE     : Office: How to Add/Remove a Single Office Program or
               Component