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.

Programming Example

http://www.microsoft.com/support/supportnet/overview/overview.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.

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:

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

Additional query words: XL97 Event Query

Keywords : kbdta OffVBA xlquery KbVBA
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbhowto