XL2000: How to Use the Query BeforeRefresh and AfterRefresh Events (213187)

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 New Database Query.
2. On the Databases tab in the Choose Data Source dialog box, click <New Data Source>, click to 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 that asks what you want to name the data source.
4. In the second box that asks you to select a database driver, select Microsoft dBase Driver (*.dbf).
5. Click Connect.
6. In the ODBC dBase Setup dialog box, click to 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\1033

   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. Select the Test data source in the Choose Data Source dialog box, and then click OK.
    
    Microsoft Query starts and displays the Add Tables dialog box.
12. Click Customer.dbf, click Add, and then click Close.
13. Double-click the * character listed in the Customer table.
    
    All the fields appear in the data pane.
14. On the File menu, click Return Data to Microsoft Excel.
15. In the Returning Data to Microsoft Excel dialog box, select New worksheet, and then click OK.

This creates the query table to use with the sample macro.

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: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: 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 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?", 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
   
   End Sub
   					

   
   NOTE: To use the AfterRefresh event, in the above example, you can replace the macro name "qt_BeforeRefresh(Cancel As Boolean)" with "qt_AfterRefresh(ByVal Success As Boolean)." Also, 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, and then click Macros. In the Macro dialog box, click Initialize_It and then click Run.
11. On the Data menu, click Refresh Data.

The BeforeRefresh event runs before the actual query data is refreshed and displays the message box that asks if you want to refresh the data. If you used the AfterRefresh event, the event runs after you refresh the query 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 allows the two events to function as expected when you refresh the query table data.