PivotTableWizard Method

Microsoft Excel Visual Basic

Show All

PivotTableWizard Method

       

 PivotTableWizard method as it applies to the Worksheet object.

Creates a PivotTable object. This method doesn’t display the PivotTable Wizard. This method isn’t available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache. PivotTable object.

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

expression   Required. An expression that returns one of the above objects.

SourceType   Optional XlPivotTableSourceType. The source of the report data.

XlPivotTableSourceType can be one of these XlPivotTableSourceType constants.
xlConsolidation. Multiple consolidation ranges
xlDatabase. Microsoft Excel list or database
xlExternal. Data from another application

xlPivotTable. Same source as another PivotTable report

If you specify this argument, you must also specify SourceData. If SourceType and SourceData are 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 report. Can be a Range object, an array of ranges, or a text constant that represents the name of another report. For an external database, SourceData is an array of strings containing the SQL query string, where each element is up to 255 characters in length. You should use the Connection argument to specify the ODBC connection string. For compatibility with earlier versions of Excel, SourceData can be 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 SourceData, you must also specify SourceType. If the active cell is inside the SourceData range, you must specify TableDestination as well.

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

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

RowGrand   Optional Variant. True to show grand totals for rows in the report.

ColumnGrand   Optional Variant. True to show grand totals for columns in the report.

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

HasAutoFormat   Optional Variant. True to have Microsoft Excel automatically format the report 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 AutoPage is False, you must create the page field or fields.

Reserved   Optional Variant. Not used by Microsoft Excel.

BackgroundQuery   Optional Variant. True to have Excel perform queries for the report 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 report’s layout. Can be one of the following XlOrder constants: xlDownThenOver or xlOverThenDown. The default value is xlDownThenOver.

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

ReadData   Optional Variant. True to create a PivotTable cache that contains all records from the external database; this cache can be very large. If ReadData is False, you can set some of the fields asserver-based page fields before the data is actually read.

Connection   Optional Variant. A string that contains ODBC settings that allow Excel to connect to an ODBC data source. The connection string has the form "ODBC;<connection string>". This argument overrides any previous setting for the PivotCache object’s Connection property.

 

 PivotTableWizard method as it applies to the PivotTable and Workbook objects.

Creates a PivotTable object. This method doesn’t display the PivotTable Wizard. This method isn’t available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache.

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

expression   Required. An expression that returns one of the above objects.

SourceType   Optional XlPivotTableSourceType. The source of the report data.

XlPivotTableSourceType can be one of these XlPivotTableSourceType constants.
xlConsolidation. Multiple consolidation ranges
xlDatabase. Microsoft Excel list or database
xlExternal. Data from another application

xlPivotTable. Same source as another PivotTable report

If you specify this argument, you must also specify SourceData. If SourceType and SourceData are 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 report. Can be a Range object, an array of ranges, or a text constant that represents the name of another report. For an external database, SourceData is an array of strings containing the SQL query string, where each element is up to 255 characters in length. You should use the Connection argument to specify the ODBC connection string. For compatibility with earlier versions of Excel, SourceData can be 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 SourceData, you must also specify SourceType. If the active cell is inside the SourceData range, you must specify TableDestination as well.

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

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

RowGrand   Optional Variant. True to show grand totals for rows in the report.

ColumnGrand   Optional Variant. True to show grand totals for columns in the report.

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

HasAutoFormat   Optional Variant. True to have Microsoft Excel automatically format the report 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 AutoPage is False, you must create the page field or fields.

Reserved   Optional Variant. Not used by Microsoft Excel.

BackgroundQuery   Optional Variant. True to have Excel perform queries for the report 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 report’s layout. Can be one of the following XlOrder constants: xlDownThenOver or xlOverThenDown. The default value is xlDownThenOver.

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

ReadData   Optional Variant. True to create a PivotTable cache that contains all records from the external database; this cache can be very large. If ReadData is False, you can set some of the fields asserver-based page fields before the data is actually read.

Connection   Optional Variant. A string that contains ODBC settings that allow Excel to connect to an ODBC data source. The connection string has the form "ODBC;<connection string>". This argument overrides any previous setting for the PivotCache object’s Connection property.

Example

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

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