Integrating Excel and Access

Keeping the Query Updated with VBA

This is an ideal time to introduce you to VBA. In the example above, you might not want to have to continue pressing refresh for your data. You may want the Query to automatically refresh itself with the new parameter each time you change cell B1. If you use VBA for programming Excel, you probably know that worksheets in Microsoft Excel have events that can have code associated with them. Events, as the name indicates, are triggered when certain events happen within Excel. Generally, these events are triggered from some type of user action. The event that you want here is the Worksheet_Change event. To set up Excel to automatically refresh your query each time you change the criteria, from your worksheet, press Alt-F11 to open the Visual Basic Programming Environment. You will see several sections; focus on the Project Explorer (Figure 2-15). If you do not see it, press Ctrl-R. Next, double-click on your current worksheet in the Microsoft Excel Objects list. There should be no code in the code window on the right side of the screen. Going from left to right on the top of the code window, go to the first drop-down box and select Worksheet, and on the second drop-down box, select Change. Excel fills in the opening and closing lines of the procedure; although the opening and closing lines are shown in the following code excerpt, they should not be typed in. Refer to the code below and type it into your procedure (Figure 2-16).

 Private Sub Worksheet_Change(ByVal ...