424,294 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,294 IT Pros & Developers. It's quick & easy.

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

P: 5
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
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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

P: 5
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

Post your reply

Sign in to post your reply or Sign up for a free account.