2.5. 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 Target As Range)     Dim wks As Worksheet     Set wks = ActiveSheet     If Target.Row = 1 And Target.Column = 2 Then       wks.QueryTables(1).Refresh     End If     Set wks = Nothing     End Sub 

Figure 2-15. The Project Explorer treeview

Figure 2-16. Typing in the code, with Project Explorer on the left and the code window on the right

Excel sends a reference to the range being updated (the Target) to this procedure. Normally, this is a single cell, but it can be multiple cells since it is a range object. In the past, I have run into problems in code when I didn't anticipate people updating a range of numbers simultaneously. In the part of the procedure that you control, you declare a variable (WKS) and make it a Worksheet object. Then you set this variable to be equal to the ActiveSheet, the currently active sheet. Remembering that the parameter is in B1, note that B1 is in row 1 column 2. Since you don't want to refresh the query every time the sheet changes, the If statement tests to ensure that the Target (the cell that is changing) is cell B1. If it is, in fact, cell B1, it will refresh the QueryTable. You may replace the 1 in wks.QueryTables(1) with the name of your DataRange in double quotes. This is important if you have more than one QueryTable on the same worksheet and you are not sure which number refers to a particular QueryTable. You can also use a For Each...Next loop to iterate through all of the QueryTables. By doing this, you can refresh every QueryTable on the Worksheet. (If you are unfamiliar with VBA, Appendix B covers the use of loops, or you could use a book on VBA as a reference to become more familiar with it). In the current example, you are using only one QueryTable, so you can refer to it with the number 1. After you have completed this code, go back to your worksheet, change the value in cell B1, and press Enter. The query updates automatically.

A more traditional VB method would be to place a command button on the worksheet and use the Button_Click event to fire the code. While that method would certainly work, having the query automatically update makes this method a lot more user friendly. If you had multiple criteria and the query took more time to run, you might want to wait for a Button Click to run the query. You could still use the Worksheet_Change event to change the caption on your button to let the user know that after she changes an amount in a criteria cell, the criteria on the screen are not what the query represents.

When you are dealing with changing query criteria, I suggest taking advantage of the functionality of Excel to take query parameters from Excel cells. The possibilities for this are endless. You can have a field for a date to review data for a certain date. You can have multiple parameters represent a range of dates, values, etc. The important thing to take away from this last example is that using the Excel user interface to pull your data in no way prevents you from using VBA to extend the capabilities.

Another important concept to take away from this chapter is the difference between the Import Data menu item and the New Database Query menu item. Use the Import Data menu item when you need to bring in an entire table or use a query that is already defined (either in the database or saved in a file); you do not have the ability to filter this data. You use the New Database Query menu item when you want to apply criteria to filter the data prior to bringing it into Excel. The procedure described in this section is a good example of how you can use VBA to extend the features that you are using from the Excel user interface. In many cases, it is easier to use VBA to extend the User Interface than it is to code the functionality from scratch.