Programming Excel with VBA and .NET

Name

pivotcaches.Add(SourceType, [SourceData])

Synopsis

Creates a new pivot cache and returns a PivotCache object.

Argument	Settings
SourceType	An xlPivotTableSourceType constant indicating the source of the data to use in the pivot table. Can be xlConsolidation, xlDatabase, xlExternal, or xlPivotTable.
SourceData	If SourceType is xlConsolidation, xlDatabase, or xlPivotTable, a Range object containing the source for the pivot table. If SourceType is xlExternal, use the Connection and CommandText property to set the data source.

To create a pivot cache from a worksheet, use SourceType xlDatabase as shown here:

Sub CreateWSPivotTable(  )
    Dim pc As PivotCache, pt As PivotTable, rng As Range
    ' Create a new pivot cache (assumes active sheet is a worksheet).
    Set pc = ActiveWorkbook.PivotCaches.Add(xlDatabase, ActiveSheet.UsedRange)
    ' Create a pivot table.
    Set pt = pc.CreatePivotTable(Worksheets.Add(  ).[a3])
    ' Set the layout: add the column and row fields.
    pt.AddFields pt.PivotFields(4).Name, pt.PivotFields(3).Name
    ' Add the data field and set its formula.
    pt.AddDataField pt.PivotFields(1), , xlSum
End Sub

To create a pivot cache from a database query, use SourceType xlExternal and then set the Connection and CommandText properties. The following code creates a pivot cache and pivot table from an SQL query to the Northwind SQL Server database:

Sub CreateNwindPivotCache( ) Dim pc As PivotCache, pt As PivotTable, rng As Range ' Create a new pivot cache. Set pc = ActiveWorkbook.PivotCaches.Add(xlExternal) ...