The PivotTableWizard Method

To create a PivotTable through code, we use the PivotTableWizard method of the Worksheet object or the PivotTable object. Contrary to what you might assume, the PivotTableWizard method does not start the PivotTable wizard. Rather, it is used to create a PivotTable when applied to the Worksheet object or to modify an existing PivotTable when applied to the PivotTable object.

The syntax is:

               expression.PivotTableWizard(SourceType, SourceData, TableDestination, _
   TableName, RowGrand, ColumnGrand, SaveData, HasAutoFormat, _
   AutoPage, Reserved, BackgroundQuery, OptimizeCache, _
   PageFieldOrder, ageFieldWrapCount, ReadData, Connection)

where expression returns either a Worksheet object or a PivotTable object. As you might expect, the parameters of the PivotTableWizard method correspond to settings in the PivotTable wizard. On the other hand, the PivotTableWizard method cannot do everything that the PivotTable wizard can do. For instance, it cannot be used to specify the row, column, and data fields. (We will see how to do that a bit later.) Put another way, the PivotTableWizard method sets the properties of an empty PivotTable.

Let us go over some of the more important parameters to the PivotTableWizard method.

The optional SourceType parameter specifies the source of the PivotTable data and can be one of the following XlPivotTableSourceType constants:

Enum XlPivotTableSourceType
     xlPivotTable = -4148
     xlDatabase = 1
     xlExternal = 2
     xlConsolidation = 3
End Enum

These directly correspond to the first dialog of the PivotTable wizard, as shown in Figure 20-1.

If we specify a value for SourceType, then we must also specify a value for SourceData. If we specify neither, Excel uses the source type xlDatabase and the source data from a named range called Database. If this named range does not exist, Excel uses the current region if the current selection is in a range of more than 10 cells that contain data. Otherwise, the method will fail. All in all, this rule is sufficiently complicated to warrant always specifying these parameters.

The SourceData parameter specifies the data for the PivotTable. It can be a Range object, an array of ranges, or a text constant that represents the name of another PivotTable. For external data, this must be a two-element array, the first element of which is the connection string specifying the ODBC source for the data, and the second element of which is the SQL query string used to get the data.

The TableDestination parameter is a Range object specifying where the PivotTable should be placed. It can include a worksheet qualifier to specify the worksheet upon which to place the pivot table as well.

The TableName parameter is a string that specifies the name of the new PivotTable.

The RowGrand parameter should be set to True to show grand totals for rows in the PivotTable. Similarly, the ColumnGrand parameter should be set to True to show grand totals for columns in the PivotTable.

The SaveData parameter should be set to True to save data with the PivotTable. If it is False, then only the PivotTable definition is saved.

HasAutoFormat is set to True to have Excel automatically format the PivotTable whenever it is refreshed or whenever any fields are moved.

The PageFieldOrder and PageFieldWrapCount parameters are meaningful only when there is more than one page field, in which case these parameters specify where the page field buttons and concomitant drop-down list boxes are placed relative to one another. The PageFieldOrder parameter can be either xlDownThenOver (the default) or xlOverThenDown. For instance, if there were three page fields, then the setting:

PageFieldOrder = xlDownThenOver
PageFieldWrapCount = 2

would arrange the page fields as in Figure 20-7. This pivot table is only for illustration of the page field order. It was created from the original pivot table by moving the row fields to page fields. Note also that setting PageFieldOrder to xlOverThenDown would simply reverse the positions of Store City and Store Type.

Figure 20-7. Illustrating page field order

The following code ostensibly creates the PivotTable in Figure 20-6 at the location of the active cell:

ActiveSheet.PivotTableWizard _
   SourceType:=xlDatabase, _
   SourceData:="'Source'!R1C1:R145C7", _
   TableName:="Sales&Trans"

In fact, the results of executing this code are shown in Figure 20-8. The reason nothing much seems to have happened is that, as we mentioned earlier, the PivotTableWizard method does not allow us to specify which fields are page, row, column, and data fields. The table in Figure 20-8 is an empty PivotTable.

Figure 20-8. An empty PivotTable