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