Hi,

One way to get started is to try recording it to see the code you get...

Alternatively, if you don't like the code you get (and I definitely did NOT
!), then use the VBA 'CopyFromRecordset' method - passing your ADO Recordset
as the argument. This will dump the data from your ADO recordset on the
worksheet, where you can use it as the source for your PivotTable...

This method is very fast, and will output the entire recordset to the
worksheet starrting at the range you specify (range.CopyFromRecordset
adorecordset !)

Once you have the data in one sheet, you can call the refresh method of the
PivotTableCache object...as well as manipulate fields, columns, hide stuff,
colour it in, do whatever you want...

We built an entire application in VB to create complex reports in Excel (we
didn't like the formatting options that Crystal Reports 7 gave us for MS
Excel Output !), format, refresh Pivot Tables using the raw data as the data
source...

good luck, let us know how you get on...

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