Export Query Results, in Pivot Table view, to Excel

[kmacd6951]

I run Macro's every day for a couple of Queries.
The Macro displays the output in Pivot Table view.

I then have to click the button on the toolbar to export this to Excel. I can't seem to find a way through the Macro to make this happen automatically(Granted, I am not doing anything fancy (ie VBA code) for my macro... just trying to use the simple Macro Wizard screen.)

When I used the "TransferSpreadsheet' option, it only sends the raw data to Excel, not the pivot table view.

Is there a way to do this? Seems that it shouldn't be that complicated.

The reason I am doing this is that not everyone who needs to see these daily reports has Access, but everyone has Excel, so I need to save them as Excel files.

Thanks

 

[Dugantrain]

The Export To Excel option is actually the equivalent of a Macro's OutputTo option. The gain with a Macro (or even better, with VBA) is that you can specify a default filepath that you'd like.

 

[kmacd6951]

I get an error when I try the OutputTo option.

"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. "


I verified that my database is not Read Only
It isn't converted.


I 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;

Any idea what is causing this error?

 

[kmacd6951]

One other thing...

I inserted the action "SelectObject" above "OutputTo". This allowed the 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.