WorksheetBase.PivotTableWizard Method

  • Article

Creates a Microsoft.Office.Interop.Excel.PivotTable object.

Namespace:  Microsoft.Office.Tools.Excel
Assembly:  Microsoft.Office.Tools.Excel.v4.0.Utilities (in Microsoft.Office.Tools.Excel.v4.0.Utilities.dll)

Syntax

'Declaration
Public Function PivotTableWizard ( _
    sourceType As Object, _
    sourceData As Object, _
    tableDestination As Object, _
    tableName As Object, _
    rowGrand As Object, _
    columnGrand As Object, _
    saveData As Object, _
    hasAutoFormat As Object, _
    autoPage As Object, _
    reserved As Object, _
    backgroundQuery As Object, _
    optimizeCache As Object, _
    pageFieldOrder As Object, _
    pageFieldWrapCount As Object, _
    readData As Object, _
    connection As Object _
) As PivotTable

public PivotTable PivotTableWizard(
    Object sourceType,
    Object sourceData,
    Object tableDestination,
    Object tableName,
    Object rowGrand,
    Object columnGrand,
    Object saveData,
    Object hasAutoFormat,
    Object autoPage,
    Object reserved,
    Object backgroundQuery,
    Object optimizeCache,
    Object pageFieldOrder,
    Object pageFieldWrapCount,
    Object readData,
    Object connection
)

Parameters

  • sourceData
    Type: System.Object
    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
    Type: System.Object
    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.
  • rowGrand
    Type: System.Object
    true to show grand totals for rows in the report.
  • columnGrand
    Type: System.Object
    true to show grand totals for columns in the report.
  • saveData
    Type: System.Object
    true to save data with the report; false to save only the report definition.
  • hasAutoFormat
    Type: System.Object
    true to have Microsoft Office Excel automatically format the report when it is refreshed or when fields are moved.
  • autoPage
    Type: System.Object
    Valid only if SourceType is xlConsolidation. true to have Excel create a page field for the consolidation. If AutoPage is false, you must create the page field or fields.
  • backgroundQuery
    Type: System.Object
    true to have Excel perform queries for the report asynchronously (in the background). The default value is false.
  • optimizeCache
    Type: System.Object
    true to optimize the PivotTable cache when it is constructed. The default value is false.
  • pageFieldWrapCount
    Type: System.Object
    The number of page fields in each column or row in the PivotTable report. The default value is 0 (zero).
  • readData
    Type: System.Object
    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 as server-based page fields before the data is actually read.
  • connection
    Type: System.Object
    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.

Return Value

Type: Microsoft.Office.Interop.Excel.PivotTable
The Microsoft.Office.Interop.Excel.PivotTable object.

Remarks

This method does not display the PivotTable Wizard. This method is not 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.

Optional Parameters

For information on optional parameters, see Optional Parameters in Office Solutions.

Examples

The following code example uses the PivotTableWizard method to create a PivotTable report based on the contents of the range of cells from A1 to C4 of the current worksheet.

This example is for a document-level customization.

Private Sub CreatePivotTable()
    Me.Range("A1").Value2 = "Date"
    Me.Range("A2").Value2 = "March 1"
    Me.Range("A3").Value2 = "March 8"
    Me.Range("A4").Value2 = "March 15"

    Me.Range("B1").Value2 = "Customer"
    Me.Range("B2").Value2 = "Smith"
    Me.Range("B3").Value2 = "Jones"
    Me.Range("B4").Value2 = "James"

    Me.Range("C1").Value2 = "Sales"
    Me.Range("C2").Value2 = "23"
    Me.Range("C3").Value2 = "17"
    Me.Range("C4").Value2 = "39"

    Dim table1 As Excel.PivotTable = _
        Me.PivotTableWizard(Excel.XlPivotTableSourceType.xlDatabase, _
            Me.Range("A1", "C4"), TableName:="PivotTable1", RowGrand:=False, _
            ColumnGrand:=False, SaveData:=True, HasAutoFormat:=False, _
            PageFieldOrder:=Excel.XlOrder.xlDownThenOver)
End Sub

private void CreatePivotTable()
{  
    this.Range["A1", missing].Value2 = "Date";
    this.Range["A2", missing].Value2 = "March 1";
    this.Range["A3", missing].Value2 = "March 8";
    this.Range["A4", missing].Value2 = "March 15";

    this.Range["B1", missing].Value2 = "Customer";
    this.Range["B2", missing].Value2 = "Smith";
    this.Range["B3", missing].Value2 = "Jones";
    this.Range["B4", missing].Value2 = "James";

    this.Range["C1", missing].Value2 = "Sales";
    this.Range["C2", missing].Value2 = "23";
    this.Range["C3", missing].Value2 = "17";
    this.Range["C4", missing].Value2 = "39";

    Excel.PivotTable table1 = this.PivotTableWizard(
        Excel.XlPivotTableSourceType.xlDatabase, this.Range["A1", "C4"],
        missing, "PivotTable1", false, false, true, false, missing,
        missing, false, false, Excel.XlOrder.xlDownThenOver, missing,
        missing, missing);
}

.NET Framework Security

See Also

Reference

WorksheetBase Class

Microsoft.Office.Tools.Excel Namespace