PivotTableWizard Method

Applies To

PivotTable object, Worksheet object.

Description

Creates a PivotTable. This method doesn't display the PivotTable Wizard.

Syntax

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

expression Required. An expression that returns a Worksheet or PivotTable object.

SourceType Optional Variant. The source of the PivotTable data. Can be one of the following XlPivotTableSourceType constants.

Constant

Description

xlConsolidation

Multiple consolidation ranges

xlDatabase

Microsoft Excel list or database

xlExternal

Data from another application

xlPivotTable

Same source as another PivotTable


If you specify this argument, you must also specify SourceData. If SourceType and SourceData omitted, Microsoft Excel assumes that the source type is xlDatabase, and the source data comes from the named range "Database." If this named range doesn't exist, Microsoft Excel uses the current region if the current selection is in a range of more than 10 cells that contain data. If this isn't true, this method will fail.

SourceData Optional Variant. The data for the new PivotTable. Can be a Range object, an array of ranges, or a text constant that represents the name of another PivotTable. For an external database, this is a two-element array. The first element is the connection string specifying the ODBC source for the data. The second element is the SQL query string used to get the data. If you specify this argument, you must specify SourceType. If the active cell is inside the SourceData range, you must specify TableDestination.

TableDestination Optional Variant. A Range object specifying where the PivotTable should be placed on the worksheet. If this argument is omitted, the PivotTable is placed at the active cell.

TableName Optional Variant. A string that specifies the name of the new PivotTable.

RowGrand Optional Variant. True to show grand totals for rows in the PivotTable. False to omit grand totals for rows.

ColumnGrand Optional Variant. True to show grand totals for columns in the PivotTable. False to omit grand totals for columns.

SaveData Optional Variant. True to save data with the PivotTable. False to save only the PivotTable definition.

HasAutoFormat Optional Variant. True to have Microsoft Excel automatically format the PivotTable when it's refreshed or when fields are moved.

AutoPage Optional Variant. Valid only if SourceType is xlConsolidation. True to have Microsoft Excel create a page field for the consolidation. If False, you must create the page field or fields.

Reserved Optional Variant. Not used by Microsoft Excel.

BackgroundQuery Optional Variant. True if queries for the PivotTable are performed asynchronously (in the background). The default value is False.

OptimizeCache Optional Variant. True to optimize the PivotTable cache when it's constructed. The default value is False.

PageFieldOrder Optional Variant. The order in which page fields are added to the PivotTable layout. Can be one of the following XlOrder constants: xlDownThenOver or xlOverThenDown. The default value is xlDownThenOver.

PageFieldWrapCount Optional Variant. The number of PivotTable page fields in each column or row. The default value is 0 (zero).

ReadData Optional Variant. True to create a pivot cache containing all records from the external database; this cache may be very large. If False, some fields can be set to be server-based page fields before the data is actually read.

Connection Optional Variant. A string that contains ODBC settings that allow Microsoft Excel to connect to an ODBC data source; a URL that allows Microsoft Excel to connect to a Web data source; or a file that specifies a database or Web query. Overrides any previous setting of the Connection property of the PivotCache object.

Example

This example creates a new PivotTable from a Microsoft Excel database (contained in the range A1:C100).

ActiveSheet.PivotTableWizard xlDatabase, Range("A1:C100")