Specifying Directory and File Name on Pivot Table Export from Access query to Excel

Marisol2

I have some queries in Access 2003 db that I have setup to display as pivots. I can go into design pivot table view and click on and then click on Export to Office MicroSoft Excel. The problem is I have about 6 queries that produce 48 reports (based on parameters). I don't want to manually save each pivot query results.

The first macro I created to export the queries I used TransferSpreadsheet but the export results are regular xls spreadsheets-with just the data and not in the pivot table views I designed.

So what I did next was created a macro that opens each query and then did a run command PivotTableExportToExcel. This is all fine and dandy, but I still have to save each Excel file it pops up manually.

I've been unsuccessful at getting Access to behave the same way that TransferSpreadsheet does in where I can specify the file name for each query. Any ideas are welcome. Thank you.

Oct 29 '08 #1

Stewart Ross

Hi. We would appreciate it if you do not double-post your questions; you have already asked about this in a thread originated by another user.

As far as I can see there is no way to save the result of performing

DoCmd.RunCmd acCmdPivotTableExportToExcel

which is really just a means to automate a menu selection. I reckon if you need to save the sheets automatically you will need to investigate the use of Excel as an automation server, running appropriate code to save the results. As I have not investigated this in the context of exporting a pivottable I cannot give further guidance on it at this stage.

-Stewart

Oct 29 '08 #2

Marisol2

Thank you. I think. My intentions were innocent as the other question was very similiar (not exact) to what I needed. Plus it was already resolved. I guess I don't understand the harm in asking similiar questions that are in the same vein, but nonetheless I won't do it again. Much apologies!

Oct 29 '08 #3