Exporting PivotChart in Access 2007

[Mr. Plow]

I've got a simple database in Access 2007, part of which creates a pivotchart. I would like to export the chart (preferably into Excel, but I could also accept a jpg or gif file). I've tried the M$ site suggestions:


Me.PivotTable.Export stDocName, plExportActionOpenInExcel

or

Me.ChartSpace.ExportPicture "D:\PivotChart.gif", , 1024, 1024

However, with both of these I get the following message:

"The expression you entered refers to an object that is closed or doesn't exist."

Do any of the learned minds here have any suggestions as to how to fix this problem. Any help would be greatly appreciated.


My code for the procedure is:

Private Sub Chart2XL_Click()
On Error GoTo Err_Chart2XL_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Data Chart"
DoCmd.OpenForm stDocName, acFormPivotChart, "", "", , acNormal
Me.PivotTable.Export stDocName, plExportActionOpenInExcel



Exit_Chart2XL_Click:
Exit Sub

Err_Chart2XL_Click:
MsgBox Err.Description
Resume Exit_Chart2XL_Click

End Sub

 

[huh?]

To export pivottables/pivotcharts to excel do the following:

1. Create a form which has your query in pivotchartview. For instructions on how to do this see <a href="http://www.mrexcel.com/forum/showthread.php?t=327283">this post</a>.
   
   
2. If you are using a button on a form to export the pivotchart, i.e. user clicks this button and it exports, then goto the form with this button, click on menu's View, click on Design View, right click on button, click on Properties, click on Event tab, click on "...." to the right of On Click, double click on Expression Builder. The visual basic interface should open and your cursor should automatically be in the correct place for the On Click event for the button. Type in:
   

   Call ExportChart("Your Form's name")

3. On the top top left of the visual basic interface is a window. Scroll down to the bottom of it. Right click on Modules, click on Insert, click on Module (this is a standard module). In the right hand pane, paste the code below:
   

   Sub ExportChart(Formname As String)
   
   Dim ChForm As Object
   
   DoCmd.OpenForm Formname, acFormPivotChart
   Set ChForm = Forms.Item(Formname)
   ChForm.PivotTable.Export "X:\YourFolder\YourFile.xls", plExportActionOpenInExcel
   DoCmd.Close acForm, Formname, acSaveNo
   Set ChForm = Nothing
   
   End Sub

   
   Save everything. When prompted for a name for the module you can type in ExportChartModule or whatever you want.
   

Disclaimer: This was tested only on access 2003.

Hope this helps

 

[JohnDozer]

I found a bug in the code. When defined as a Sub, Access cannot locate the code when you click the button.

To remedy this use

[B]Function[/B] ExportChart(Formname As String)

Dim ChForm As Object

DoCmd.OpenForm Formname, acFormPivotChart
Set ChForm = Forms.Item(Formname)
ChForm.PivotTable.Export "X:\YourFolder\YourFile.xls", plExportActionOpenInExcel
DoCmd.Close acForm, Formname, acSaveNo
Set ChForm = Nothing

End [B]Function[/B]

 

[sirwebber]

In your code, I noticed that you use PivotTable.Export. Is there a way to directly export a pivot chart? PivotChart.Export does not seem to work. Thanks for any help you can provide!