Pivot Table

ADO

Recordset



From the help example from xl2003:



This example creates a new PivotTable cache using an ADO connection to
Microsoft Jet, and then it creates a new PivotTable report based on the
cache, at cell A3 on the active worksheet.

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command

' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open "C:\perfdate\record.mdb"
End With

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = "Select Speed, Pressure, Time From DynoRun"
.CommandType = adCmdText
.Execute
End With

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
.CreatePivotTable TableDestination:=Range("A3"), _
TableName:="Performance"
End With

With ActiveSheet.PivotTables("Performance")
.SmallGrid = False
With .PivotFields("Pressure")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Speed")
.Orientation = xlColumnField
.Position = 1
End With
With .PivotFields("Time")
.Orientation = xlDataField
.Position = 1
End With
End With

' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
Set cnnConn = Nothing
-- Regards,Tom Ogilvy"MChrist" wrote in
message ...
Philip,

Thank you for your response. I was looking for a way to avoid pasting the
records to a spreadsheet by manipulating the recordset directly. While I
can
create a pivot table (quite a coding pain!), I wasn't able to figure out
how
to remove the row or column subtotals, or to apply the recordset to the
existing table.

Basically, I had to go back to what I had. If anyone can point me to an
example that doesn't require rebuilding the entire pivot table, I would
greatly appreciate it.

Thanks again though.

Mark

"Philip" wrote:

Code:
1) get the data from an ADO recordset into an Excel sheet:

Range.copyfromrecordset adorecordset

2) refresh the pivottable to use that data:

PivotTable.RefreshTable

In my last reply, I said use "Pivotcache.refresh" but of course, this
causes
the query to be re-run if you have a dynamic query, wheras it seems you
will
have static data in a table (after using the CopyFromrecordset method),
thus
no need for that, you can just use RefreshTable ...

HTH

Philip


"MChrist" wrote:

I have a stored procedure on an MS SQL Server that I can retrieve
records
from that I would like to place in the pivot table I have. So far,
using ADO
code and the copyfromrecordset method, I've been able to paste the
records
into a tab in the workbook and update the pivot table, but a couple
people
suggested I create/update the pivot table directly without pasting the
records to the spreadsheet.

I've tried looking in help for Pivot Table/Pivot Cache, and I get bits
and
pieces, but things like ADO connections I don't get results even if I
have
the reference attached.

Could someone please point me to an example of how to use the
recordset
directly by a pivot table. I appreciate any help you can provide.

Thank you

Mark