Query Table Events

A query table is a table in an Excel worksheet that represents data returned from an external data source, such as an SQL Server database, a Microsoft Access database, a web page, or a text file. A query table is represented by the QueryTable object. Excel provides two events for the QueryTable object: BeforeRefresh and AfterRefresh. To try out the example procedures demonstrated in the table at the end of this section, perform the tasks outlined below. This exercise assumes that you have Microsoft Access and its sample Northwind database installed on your machine.

1. In the Microsoft Excel application window, choose Data | Import External Data and select New Database Query to create a new database query.

2. In the Data Source dialog box, choose <New Data Source> and click OK.

3. In the Create New Data Source dialog box, enter SampleDb as the data source name.

4. Select the Microsoft Access (*.mdb) driver from the drop-down list next to step 2 in the Create New Data Source dialog box.

5. Click the Connect button.

6. In the ODBC Microsoft Access Setup dialog box, click the Select button.

7. In the Select Database dialog box, locate the Northwind.mdb file. This file usually can be found in the C:\Program Files\Microsoft Office\Office\Samples folder.

8. Select the file and click OK to close the Select Database dialog box.

9. Click OK again to exit the ODBC Microsoft Access Setup dialog box.

10. In the Create New Data Source dialog box's step 4, select the Categories table in the drop-down list box.

11. Click OK to close the Create New Data Source dialog box.

12. In the Choose Data Source dialog box, the SampleDb data source name should now be highlighted. Click OK.

13. In the Query Wizard - Choose Column dialog box, click the > button to move all the fields from the Categories table to the Columns in your query box.

14. Click the Next button until you see the Query Wizard - Finish dialog.

15. In the wizard's Finish dialog box, make sure the Return Data to Microsoft Excel option button is selected and click Finish.

16. In the Import Data dialog box, the current spreadsheet cell is selected. Click cell A1 in the current worksheet to change the cell reference, and click OK to close the dialog box.

After completing the above steps, the data from the Category table in the Northwind database should be placed in the active worksheet. It took quite a number of steps to retrieve this data. In the next chapter, you will learn how to create a query table programmatically.

To write event procedures for a QueryTable object, you must create a class module and declare a QueryTable object by using the WithEvents keyword.

1. Insert a class module to the current VBA project and rename it clsQryTbl.

2. In the clsQryTbl [Code] window, type the following statement: Public WithEvents qrytbl As QueryTable

After you've declared the new object (qrytbl) by using the WithEvents keyword, it appears in the Object drop-down list in the class module.

3. In the clsQryTbl [Code] window, enter the two event procedures presented in the table on the following page: QryTbl_BeforeRefresh and QryTbl_AfterRefresh. Before you can trigger these event procedures, you must connect the object that you declared in the class module (qrytbl) to the specified QueryTable object.

4. Insert a standard module into the current VBA project, and enter the following code:

Dim sampleQry As New clsQryTbl

Public Sub Auto_Open()

' connect the class module and its objects with the Query object Set sampleQry.qrytbl = ActiveSheet.QueryTables(1) End Sub

The above procedure creates a new instance of the QueryTable class (clsQryTbl) and connects this instance with the first query table on the active worksheet. The Auto_Open procedure will run automatically when you open the workbook, so you will not need to run this procedure manually to ensure that the query events that you've entered are triggered when the data is refreshed by the user or the system.

5. Run the Auto_Open procedure that you entered in step 4. After you run this initialization procedure, the object that you declared in the class module points to the specified QueryTable object.

6. In the worksheet where you placed the Category table from Microsoft Access, change the name of one of the retrieved categories. Select any cell in the query table and click the Refresh button on the External Data toolbar, or choose Data | Refresh Data.

At this time, the event procedure qryTbl_BeforeRefesh will be triggered, and you should see the custom message box. If you click Yes, the data will be refreshed with the existing data in the database and the change in the name of the category you've made will be overwritten.




This event occurs before the query table is refreshed.

Private Sub qryTbl BeforeRefresh(Cancel As Boolean) Response = MsgBox("Are you sure you "

& " want to refresh now?", vbYesNoCancel) If Response = vbNo Then Cancel = True End Sub


This event occurs after a query is completed or canceled. The Success argument is True if the query was completed successfully.

Private Sub qryTbl AfterRefresh(ByVal Success As Boolean) If Success Then

MsgBox "The data has been refreshed."


MsgBox "The query failed." End If End Sub

0 0

Post a comment