Export Pivot Table to Excel

[mlr0911]

Hello, I was hoping someone could help me with exporting a pivot table created in Access to export and save to an excel workbook.

The current code I have in place works fine, however, I have to specify the location and "saveas" type each time I want to export the pivot table to excel:

DoCmd.OpenQuery "User_Authorization", acViewPivotTable
DoCmd.RunCommand acCmdPivotTableExportToExcel
DoCmd.Close acQuery, "User_Authorization"

I've used the docmd.transferspreadsheet method in the past and don't know if there is another method for the pivot table?

Is there an easier way of doing this? I would like to export the pivot table(s) because there is about 20 I have to do for each autorization group. I'd like to make it faster where I can loop through the records.

Any help/suggestions would greatly be appreciated.

 

[Banana]

Why not simply export directly from pivottable itself?

(assuming code is in the module behind the form)

Me.PivotTable.Export , plExportActionOpenInExcel

Reference

 

[mlr0911]

Hi Banana

I have the pivottable executing off of a query, I don't have it built on a form. I tried building it on a form, but it didn't seem to always refresh the data properly....may have been something I was doing wrong. I've tried to use the code above to export, but when it does export, there are 2 files being generated an XLS file and an XML file and it won't let me open them.

 

[Banana]

I see. Did you use the code as it is? On the form or on the query?

I can't say I've gotten two files, a XLS and a XML. I've found it more convenient for the users to just open it in Excel and let them handle the save operation, which is why I omit the filename parameter and use plExportActionOpenInExcel parameter.

As for basing it on form and not refreshing the data correctly. I think I need more details as to what is meant by refreshing?