Export Access Pivot Table to Access

[kennedymr2]

I am using Access 2010

Trying to write a function to include in a macro
to export a pivot table to excel.

something like !!!!!


DoCmd.TransferSpreadsheet acCmdPivotTableExportToExcel, acSpreadsheetTypeExcel7, "datatabpivot", strFullPath & "Daily.xls", False


I realize that acCmdPivotTableExportToExcel is wrong !@@!@!



Would appreciate any advice as to how to achieve this (if possible)

Regards Kennedymr2

 

[PHV]

How would you do it manually ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kennedymr2]

When the pivot Table is loaded and visual...

There is a button at the top export to excel


When this is pressed it creates a spreadsheet..
I also then need to save it auto as well


Regards Kennedymr2

 

[PHV]

So, have you tried something like this ?
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "datatabpivot", strFullPath & "Daily.xls"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kennedymr2]

PHV (MIS)

I tried using this but it will not export a pivot table only a normal table

Regards Kennedymr2

 

[PHV]

Did you try to export a crosstab query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kennedymr2]

PVH

Appreciate your giving time to my post...

Not real sure about export a crosstab query....i am trying to export a pivot table...

The acexport only seems to be able to export a table


Regards Kennedymr2

 

[kennedymr2]

Just thought !!!!

Ie the pivot table is a Form

Maybe a form cannot be exported to excel

 

[kennedymr2]

Finally solved it !!!!!

There is no docmd. for this in Access 2010

Solved...

Public Function ExportPT()
Dim BPT As Office.CommandBarPopup
Dim BEPT As Office.CommandBarButton
Set BPT = Application.CommandBars("Menu Bar").Controls("PivotTable")
Set BEPT = BPT.Controls("Export to Excel")
BEPT.Execute
End Function


ie it automates the button on the menu bar...