ACCESS Export PivotTable & Tables to Excel

[spoonjy]

Hi,
i have generated many tables, and pivot tables in Access. and i want to write codes to export all of them to one single excel sheet.
i have little knowledge about this export function.
may i know how to export pivot tables and normal tables to one single excel file?
thanks in advance.

[RobDog888]

If you perform a loop with this you can export all the tables you need to a single excel file but you will have to change sheets target range, adjusting it to compensate for the end of each range of data or just change the destination to a different sheet each time. Then merge the data to a single sheet later if desired.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "D:\Book1.xls", False, "Sheet1$"

[spoonjy]

Thanks. How about exporting Pivot Table?
that code works for normal tables. how about pivottables?

[RobDog888]

If the pivit table is saved just like a regular query, then pass its name instead of "Table1".

[spoonjy]

pivot table is not a query, it is a form.
so the command doesnt work..
may i know how to export the pivot table to excel file? i want the output just like the way of tables export.

[RobDog888]

No, I mean to try passing the pivot table name if that doesnt work then I guess you have to go the route of automating the menu.

[RobDog888]

I wrote a small basic example for you. You well want to add error trapping and testing for menu item existance, etc.

Private Sub ExportPivotTable()
    'Add a reference to MS Office xx.0 Object Library
    Dim oCBPT As Office.CommandBarPopup
    Dim oCBEPT As Office.CommandBarButton
    
    Set oCBPT = Application.CommandBars("Menu Bar").Controls("&PivotTable")
    Set oCBEPT = oCBPT.Controls("E&xport to Microsoft Office Excel")
    oCBEPT.Execute
End Sub

[spoonjy]

thanks for the reply.
may i know what is the route of automating the menu?
is there any sample command that i can refer to for exporting this pivot table?
there is actually one button on the tool bar. by clicking that, i can export it to a temporary excel file. but i want to achieve this by using vba and specifying the file name and the worksheet name. so my whole process of export can go to one single file.

thanks in advance for all the help.

[RobDog888]

Oh, forgot to mention that it was for Access 2003. So if your running a different version or language then the menu captions should be changed to either index numbers or the appropriate context.

I see we posted at almost the same time.

[spoonjy]

WOW, that is great!
I see that you are executing that button on the menu bar!!
thanks a lot for the help.
juz one further question. cos by doing that, i can only export to a temporary excel file.
how can i specify a file that i wish to export to? because i have other tables and queries to export as well and i want to group them all in one single excel file.

Thanks sincerely for the help!

[RobDog888]

Without really getting into some APIs, its not going to be very easy to do it from the current method. Also, I have a few other methods but they have the same issues of not being able to specify a output filename.

'Open your pivot form first
DoCmd.OpenForm "PivitTable1", acFormPivotTable
'Then run either of the two...They output it to Excel a little differently from each other.
DoCmd.RunCommand acCmdPivotTableExportToExcel
'Or
DoCmd.RunCommand acCmdOutputToExcel

I suppose we could just use the Excel Object Model in Access to attach to the exported temp workbook. Then write it into a cummulative Workbook file. a bit messy but I think I can come up with a better way in the morning after I get some sleep.

[spoonjy]

hi, thanks for putting so much effort for helping me..
do u have a good method now? juz to export the pivot table to a specific excel file and specific sheet.
thanks again robdog!!

[RobDog888]

Sorry, I've been busy but to continue on with my last suggestion would be to use the Excel Object Model and manually export it. But first you need to generate a SQL statement that will be a duplication of your pivot table. Then save it as a query. After you have that you can easily do a .TransferSpreadSheet to a specific workbook and sheet/range.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "PivotQuery1", "D:\Book1.xls", False, "Sheet2$"

[WestwoodVB]

When I use this code I get the following...

The command or action 'acCmdPivotTableExportToExcel' isn't available now

Any thoughts?

[Natural]

Good Morniing

I have been trying to achieve simular but also get the error

The command or action 'acCmdPivotTableExportToExcel' isn't available now

And been batteling to get this to work..

I currently in MS access 2003 have

I have a form called FrmEmployeeCountPivot

And on the above mentioned i have a subform called FrmEmployeeCountPivotSub

The Sub is the Pivot Table

What i am trying to achieve is normally i will click on my pivot table comandbar, on the export to excell. And will work perfectly.

Now Because the sub is on my main form my Pivot Commandbar, does not appear anymore.

I would like to place the command on my main form to allow the user the send the pivot to excell exactly as it happens if i click the send to excel command on my menu..

Is there a way that use the send to excel menu command just behind an normal cmdbutton on my form

I tried using the above

Private Sub ExportPivotTable()   
 'Add a reference to MS Office xx.0 Object Library    
Dim oCBPT As Office.CommandBarPopup    
Dim oCBEPT As Office.CommandBarButton       
 Set oCBPT = Application.CommandBars("Menu Bar").Controls("&PivotTable")    
Set oCBEPT = oCBPT.Controls("E&xport to Microsoft Office Excel")    oCBEPT.ExecuteEnd Sub

but only get an error,

is there a way that i can make use of the command on my subform.

Thank you very much in advance for any help