Programming Excel with VBA and .NET

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