OutputTo - Access Pivot table to Excel

[kmacdonald]

I run macro's in Access that create Pivot table's for me each AM. I would like to take the automation one step futher and have the pivot tables export to Excel. Currently, I am going to the Access Pivot table and clicking the button that 'exports to Excel' (little icon of Excel with a pencil). This icon is only available when you are in pivot table view.

Using TransferSpreadsheet doesn't work, as that only exports my data in spreadsheet layout.

I tried the OutputTo option, but I get an error:
"The Command or action 'OutputTo' isn't available now
* You may be in a read-only database or an unconverted database from an earlier version of Microsoft Office Access
*The type of objec the action applies to isn't currently selected or isn't the active view

Use only those commands and macro actions that are currently available for this database."

My macro is set up as follows:
have 4 actions set in this macro:

OpenQuery : View is Pivot Table; Data Mode is Edit
Set Warnings: Warnings on = no
Hourglass: Hourglass on = Yes
OutputTo: Object Type: Query; Object Name: Call Query Closed; Output Format: Microsoft Excel 97-2003 (*.xls); Output File: c:\metrics\schedule\closed call; Autostart: No;

I then tried to insert the action "SelectObject" above "OutputTo". This allowed the data to output, but now it is not outputting as an .xls file, even though I have specified that format. I am not even sure what it is output as - windows can't recognize it.

Select Object: Object Type - Query; Object Name -Call Query Closed; In Database Window - Yes.

Any ideas what I am missing?
Thanks

 

[Pat Hartman]

You can't export Access objects (except to another Access db). You can only export data. If you already have a pivot table, what is the point of exporting to Excel?

If you want to work in Excel:
Create a spreadsheet that contains ONLY the data.
Create a spreadsheet that has a pivot table that gets its data from the first spreadsheet.
Export using the TransferSpreadsheet Method to the first spreadsheet.

 

[kmacdonald]

You can't export Access objects (except to another Access db). You can only export data. If you already have a pivot table, what is the point of exporting to Excel?

If you want to work in Excel:
Create a spreadsheet that contains ONLY the data.
Create a spreadsheet that has a pivot table that gets its data from the first spreadsheet.
Export using the TransferSpreadsheet Method to the first spreadsheet.

Thank you! That is what I was missing - that you can't export objects!

The reason I am needing to send it to Excel is that the people I have to distribute the report to, don't have Access, only Excel! :-(