| If you've worked much with Excel's pivot tables, you're surely familiar with a list as a pivot table's data source. And you could use the data as shown in Figure 4.23 as a pivot table source, without any further manipulation. Refer to Figure 4.15, which shows the Import Data window, which appears when you're through defining a query with Microsoft Query or with the Query Wizard. You'll notice in that window there's an option to create a pivot table report (the exact wording depends on the version of Excel that you're using). If you select this option, you see the final step in the Pivot Table Wizard instead of the external data range with its records and fields. There's nothing special about this capability. You get to the same place by taking these steps: - Start the Pivot Table Wizard by choosing PivotTable and PivotChart Report from the Data menu.
- Select External Data Source in the wizard's first step.
- Click Get Data in the wizard's second step.
You'll then see the Choose Data Source window, just as in Figure 4.2. You can now select an existing data source, or create a new one. Eventually you'll finish structuring the query with Microsoft Query (or, if you choose, with the Query Wizard) and when you return the data to Excel, you'll return to the PivotTable Wizard's final step. The process and the end result are the same, no matter whether you start with the PivotTable Wizard or the Import External Data command. Populating Pivot Tables A pivot table stores its data in a special location, termed the cache. The cache makes it possible for a pivot table to carry out complex recalculations very quickly. But the cache has drawbacks. You might have noticed that if a pivot table is based on data in an Excel list, and if the data in that list changes, the pivot table does not change in response. To get this to happen, you have to prompt it by choosing Data, Refresh Data, or clicking the Refresh button on the pivot table toolbar, or one of at least two other methods. The same is true if you base a pivot table on what Excel terms external data. Suppose that you use either the PivotTable Wizard or the Import External Data command to create a pivot table that summarizes records that are found in a database. Subsequently, the data in the database changes. Just as though the pivot table were based on an Excel list, you have to refresh the pivot table. Refreshing the pivot table updates the cache, and when the cache is updated, the pivot table itself is updated. TIP If you want, you can suppress the cache. One of the pivot table options is to Save Data with Table Layout. Unchecking this option means that the pivot table has no cache, on which a pivot table's functionality largely depends. You can save a little space and time by suppressing the cache. If you ever need a cache to pivot the table, for example you simply choose Data, Refresh External Data.
Refreshing the Cache There's no really satisfactory solution to the problem described in the previous section. As of Excel 2003, the cache simply does not respond automatically to changes in its data source. The best you can do is set one or more pivot table options that cause the cache to be refreshed in response to an event. Events that can refresh the cache include opening the workbook that contains the pivot table and a specified amount of time passing. Right-click a cell in the pivot table and choose Table Options. The dialog box shown in Figure 4.25 appears. Figure 4.25. It's a good idea to give the table a descriptive name, and not rely on the default name PivotTable1. 
Use the PivotTable Options dialog box to do one or both of these: Fill the Refresh on Open check box and save the workbook. When you next open the workbook, the pivot table automatically refreshes its cache from the data source. If you want a more frequent refresh, fill the Refresh Every check box and use the spinner to specify the number of minutes to wait between automatic refreshes.
The problem, of course, is that it takes time and system resources to perform the refresh. If you have a lot of data underlying the pivot table, refreshing it every (say) 5 minutes can be wasteful. In a networked environment, where you're piping hundreds of thousands of bytes of data through the network during a refresh, consider the frequency of the refreshes very carefully. TIP If your pivot table's data is in an external data source, you can create the pivot table without ever putting a list in the workbook. Subsequently, though, you might find that you want to look at the individual records and fields, just as if you had first imported the data into an external data range and then built the pivot table using that range. To create the list, double-click the pivot table's Grand Total cell. This inserts a new worksheet and puts the cache's data on it in list form.
Choosing Between Refresh on Open and the Workbook_Open Event The previous section described how to make a pivot table automatically refresh its data: fill the Refresh on Open check box in the pivot table's options. You can also cause an external data range to refresh itself automatically: Right-click any cell in the data range, choose Data Range Properties from the shortcut menu, and fill the Refresh Data on File Open check box. That's pretty straightforward, but things begin to get complicated when you have more than just one thing to do when a workbook opens. For example, opening the workbook might cause a VBA subroutine to run as part of the workbook's On Open event. In that case, it can be important to know whether a pivot table, or an external data range, refreshes before or after the subroutine runs. I once spent eight nonbillable hours re-creating hundreds of pivot tables and charts that had accumulated over a five-month period, every one of them wrong, because I thought the refresh preceded the Open event. I promise you that it's important to keep in mind that the Open event precedes an automatic, on-open refresh. Or you might have several pivot tables in a workbook, each of which should refresh when the workbook opens. This can present an annoyance. If a pivot table's structure changes as a result of an automatic refresh, Excel notifies you with a message box that says Pivot table was changed during Refresh Data operation. (Whether or not you see this message depends on the version of Excel that you're using.) One alert message isn't a problem, but if you have, say, 10 or 15 pivot tables that are changing when they're refreshed, and you're getting an alert for each one, and you have to click OK to get to the next pivot table that's annoying. So, consider handling the pivot table refreshes with Visual Basic for Applications. For the purpose of refreshing pivot tables, this chapter provides an overview.  Chapter 7, "VBA Essentials Reviewed," goes into greater detail on VBA.
As mentioned previously, workbooks have Open events (among various others). The idea behind an event, in this context, is that Excel notices when the event occurs, and runs code in response to the event. This code is termed an event handler: It handles what happens when the event occurs. So, you can write VBA code that runs when the Open event occurs for its workbook, and that code can refresh your pivot tables. The rationale is that in this way you can exert greater control over when the pivot tables refresh. And not incidentally, you can suppress those annoying alerts that the pivot table changed. If you're using Excel 2003, there might be another reason yet. Excel 97 did not warn you that automatic refreshes could be based on harmful queries that could, for example, write data back to an external source. Beginning with Excel 2000, Service Release 1, you are alerted, and can choose to continue with the refresh, disable the refresh, or enable automatic query refresh for all workbooks and not see the warning again. Excel 2003 does not provide the third option, which suppresses subsequent warnings. The only way to do so is to edit the system registry or to refresh pivot tables automatically with your own VBA code. As already noted, doing the refreshes with VBA code puts you in a position to exert greater control over the timing of the refreshes. To arrange to refresh all a workbook's pivot tables using its On Open event, take these steps: - Open the workbook that contains the pivot tables.
- Right-click the Excel icon that appears directly to the left of the File menu to display a shortcut menu, and choose View Code.
TIP You can also press Alt+F11 to open the Visual Basic Editor. Once there, right-click the This Workbook icon in the Project Explorer, and then choose View Code to open the workbook's code window. You'll see a window similar to the one shown in Figure 4.26. For the purpose of building an Open event handler, it's not important that the Project Explorer or the Properties Window be visible, but if you want to, you can find them in the View menu.
Figure 4.26. The code that handles an event is associated with an Excel object, so it is not found in a Modules folder. 
- Notice in Figure 4.26 the code pane occupying the right portion of the window. It has two dropdowns at its top. The one on the left, which has (General) selected, is the Object dropdown. The one on the right, with (Declarations) selected, is the Procedure dropdown. Click the Object dropdown and choose Workbook from the list.
Two statements automatically appear in the code window: a Private Sub statement, naming the procedure (by default, Workbook_Open) and an End Sub statement, marking the end of the procedure. The Procedure dropdown also automatically selects Open (see Figure 4.27).
Figure 4.27. There are 28 procedures available as workbook events in the procedure dropdown, including Activate and NewSheet. 
- Type the code that you want to run between the Private Sub and the End Sub statements (see Figure 4.28).
Figure 4.28. Option Explicit requires that you declare variables before using them. It's good programming practice to do so. 
- Choose File, Close and return to Microsoft Excel, and then save the workbook.
To find out about Option Explicit, see "Establishing Subroutines," p.168.
Here's the code again, with some comments to explain it: Private Sub Workbook_Open()
Establish a subroutine that will run when the workbook that contains the subroutine is opened. Dim wks As Worksheet, pt As PivotTable
Declare two objects variables: wks and pt. To declare the variables is merely to inform Visual Basic that they exist, that they have these names, and the type of objects that they represent. To declare (another term is dimension, which is the source of the keyword Dim) wks as Worksheet is to state that the variable wks can represent worksheets. Just as in algebra, the variable X might represent a value such as 1, 23, or 846; the variable wks can represent Sheet1, Sheet2, or any worksheet that has any name. Similarly, to declare the variable pt as PivotTable is to state that it can represent any pivot table. For Each wks In ThisWorkbook.Worksheets
This statement initiates a loop. The subsequent statements will execute once for each instance of the variable wks. Wks will represent each worksheet in the workbook a different worksheet for each time the loop itself executes. For Each pt In wks.PivotTables
Another loop is initiated. This one runs inside the outer loop, and it will run once for each pivot table on the worksheet currently represented by wks. Suppose that Sheet1 has two pivot tables and Sheet2 has three pivot tables. When wks represents Sheet1, pt will represent the first, and then the second of the two pivot tables on Sheet1. Then, when wks represents Sheet2, pt will represent the first, and then the second, and finally the third pivot table on that worksheet. pt.RefreshTable
With wks representing a particular worksheet, and pt representing a particular pivot table on that worksheet, Visual Basic knows precisely which pivot table to refresh. The RefreshTable command does so, and it does so without making the user respond to any warning. Next pt
Go to the next pivot table on the worksheet currently represented by wks. Next wks
Go to the next worksheet in the workbook. End Sub
End the subroutine. TIP Some commands that you give VBA to execute don't warn you when something happens that you might need to know about. The example shown here, RefreshTable, is one of them. Others do warn you. Changing the orientation of a pivot table field is one of them: Excel warns you if doing so will overwrite existing data. If you want to suppress warnings temporarily, use this command: Application.DisplayAlerts = False
Handle this statement with care. Before you use it, make certain that you don't want your code to warn you of a possible problem. And reverse its effect as soon as possible with Application.DisplayAlerts = True
This subroutine is concise and exhaustive: In only eight statements, it refreshes every pivot table on every worksheet in the workbook. But it doesn't allow for much customized control. You need to micromanage the code to do that. For example, suppose that you want to refresh only two specific pivot tables when a particular workbook opens. The code might look like this: Private Sub Workbook_Open() Dim wks As Worksheet, pt As PivotTable ThisWorkbook.Worksheets("PartyStats") _ .PivotTables("Age By Party").RefreshTable ThisWorkbook.Worksheets("StateStats") _ .PivotTables("Party By State").RefreshTable End Sub
This code refreshes only two pivot tables: Age By Party and Party By State. Suppose that there were another pivot table, Hits By Date, that shows the number of hits on a Web site during each month. You might not want to update that pivot table each time the workbook is opened. A casual user could get the wrong impression if the entire month of September had 1,600 hits, and the entire month of October had 1,750 hits, and if he opened the workbook halfway through the current month, on November 15. The pivot table (and perhaps an associated chart) might show only half as many hits, month to date. Probably, in contrast to Age By Party and Party By State, the interpretation of the Hits By Date pivot table depends on when during the month the user sees it. Therefore, you could put in code to refresh the Hits By Date pivot table on only the final day of each month. | 