Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Andrzejek on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export Access Pivot Table to Access

Status
Not open for further replies.

kennedymr2

Programmer
May 23, 2001
594
AU
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

 
How would you do it manually ?

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

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

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
PHV (MIS)

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

Regards Kennedymr2
 
Did you try to export a crosstab query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
Just thought !!!!

Ie the pivot table is a Form

Maybe a form cannot be exported to excel
 
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...

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top