Pivot Command

[natural]

I was hoping someone might be capable to assist

I have a form called FrmEmployeeCountPivot

And on the above mentioned i have a subform called FrmEmployeeCountPivotSub

The Sub is a 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 creating a macro that uses
Runcommand - PivotTableExportToExcel but only get an error, action failed
is there a way that i can make use of the command on my subform.

Thank you very much in advance for any help

Regards

[hjozinovic]

Here is the code that you can put in OnClick event of your button:

stDocName = "name of query here"
DoCmd.OpenQuery stDocName, acViewPivotTable, acEdit
DoCmd.RunCommand acCmdPivotTableExportToExcel
DoCmd.Close acQuery, stDocName, acSaveNo

[natural]

HI

Thank you for the assistance.

I was wondering, this allows me to export the data in query view, and then to add it to the pivot. When it is in Excell

Is there a way to export the current pivot as it is in Access

Thank you in advance

[hjozinovic]

Yes there is. Try something like:

DoCmd.GoToControl "Subform Control Name Here"
DoCmd.RunCommand acCmdPivotTableExportToExcel

[natural]

Brillinat

Thank you so much for the help..


Regards

Natural

[Marisol2]

Sorry to ask a question since it wasn't my original post, but is there a way to specify the file path and file name for the pivot that was exported to Excel. The code works great to transfer for the pivot in it's designed format, but I still have to manually save the output after it's sent to Excel. Thanks.

[Stewart Ross]

Please do not hijack another poster's thread, or double-post your questions (see this thread). Thank you.

MODERATOR