copy pivot table

[rummey]

Hello,

I am working in Access 2003. I have a pivot table that looks very
good, and if I press CTRL+A, CTRL+C while in the PivotTable View then
it will be copied into memory. I can then do CTRL+P in Excel and it
pastes over great.

Is there a way to do this with OLE Automation (or some other way)? I
have been able to copy a PivotTable View of a Form to Excel with the
following code:

Set ptView = Forms("Form1").PivotTable
ptView.Copy ptView.ActiveView

'Then, create the new workbook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
xlApp.Application.Visible = True

'Select the cell where we need to begin the paste function
xlApp.Application.Cells(1, 1).Select
'Paste the clipboard contents here from the copied pivot table
xlApp.ActiveSheet.Paste

This works great, but I have to Save my Query as a Form named "Form1".
This is no fun!

I would really like to skip the step of saving the Query as a form
(which I can not automate) by simply copying the contents of the Query
(in its pivot table view).

Thanks in advance!

Mike

[pietlinden]

maybe you can grab the recordset and then use the code at MVPS.org to
send the recordset to Excel using automation.
See...
http://www.mvps.org/access/modules/mdl0035.htm