Programming Excel with VBA and .NET

Name

pivottables.Add(PivotCache, TableDestination, [TableName], [ReadData], [DefaultVersion])

Synopsis

Creates a new pivot table from an existing pivot cache and returns the created PivotTable object.

Argument	Settings
PivotCache	The PivotCache object to use as the data source for the pivot table.
TableDestination	The Range object indicating the location of the upper-left corner for the pivot table.
TableName	A name to assign to the pivot table. Default is PivotTable n.
ReadData	For database queries, True reads all of the fields from the data source; False delays retrieving the data until the pivot cache is refreshed. Default is True.
DefaultVersion	The Excel version assigned to pivot table. Can be xlPivotTableVersion10, xlPivotTableVersion2000, or xlPivotTableVersionCurrent.

This method is equivalent to the PivotCache object’s CreatePivotTable method. The following code creates a pivot cache from a database query and then uses that pivot cache to create a pivot table:

Sub CreateSalesThisWeekPT( ) Dim pc As PivotCache, pt As PivotTable, pf As PivotField, _ ws As Worksheet ' Create a new pivot cache for database query. Set pc = ActiveWorkbook.PivotCaches.Add(xlExternal) ' Create a connection string and SQL query. pc.Connection = "ODBC;DRIVER=SQL Server;SERVER=USERS;" & _ "UID=jeff;APP=Microsoft Office 2003;WSID=WOMBAT4;" & _ "DATABASE=Sales;Trusted_Connection=True" pc.CommandType = xlCmdSql pc.CommandText = "SELECT SalesRank, ProductName, Date FROM Amazon " & ...