ID: Q182735
The information in this article applies to:
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.
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.
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.
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.
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 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/
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.
Initialize_It macro.
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.
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
Keywords : kbdta OffVBA xlquery KbVBA
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbhowtoLast Reviewed: May 18, 1999