Microsoft Office Tutorials and References
In Depth Information
20.3 The PivotTableWizard Method
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
Search JabSto ::

Custom Search