Name
pivotcache
.Recordset [= setting
]
Synopsis
Sets or returns the ADO RecordSet
object used to create the pivot cache. The following code demonstrates how to use an ADO recordset created from a SQL Server database query to create a pivot cache and pivot table:
' Requires reference to Microsoft ActiveX Data Object library Sub CreateADOPivotCache3( ) Dim pc As PivotCache, pt As PivotTable Dim cnn As New ADODB.Connection, cmd As New ADODB.Command, _ rs As New ADODB.Recordset ' Create ADO recordset. cnn.ConnectionString = "Provider=sqloledb;data source=USERS;" & _ "initial catalog=Northwind;Integrated Security=SSPI;" & _ "persist security info=True;packet size=4096;Trusted_Connection=True" cmd.CommandText = "SELECT CategoryName, ProductName, UnitsInStock, " & _ "UnitPrice FROM Products, Categories" cnn.Open Set cmd.ActiveConnection = cnn Set rs = cmd.Execute ' Create a new pivot cache. Set pc = ActiveWorkbook.PivotCaches.Add(xlExternal) ' Use the ADO recordset as the data source. Set pc.Recordset = rs ' Create a pivot table based on the new pivot cache. Set pt = pc.CreatePivotTable(Worksheets.Add( ).[A3]) ' Set the layout: add the column and row fields. pt.AddFields "ProductName", , "CategoryName" ' Add the data field and set its formula. pt.AddDataField pt.PivotFields("UnitsInStock"), , xlSum ' Close the recordset and database connection. rs.Close cnn.Close End Sub
Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.