Need to export a pivot chart view of a query

[hughess7]

Hi, I will need to do this shortly so I am interested in your resolution
please. I am creating a very detailed report in word direct from Access via a
mail merge, and I would like to have the facility to add a chart
automatically too.

Thanks in advance for any help.
Sue

 

[Blades]

Did you finally manage to get a way to exprot access pivot cahrts to a word
document ? Would you mind sharing the method ?

 

[Blades]

I want to export Access 2003 query based pivot charts to a word document-
mind can you explain your method or even better share the program ?

 

[Robin]

Hi
After pulling my hair out like the rest of the world wide web, I found a
solution provided by "huh?" that worked for me on
http://www.mrexcel.com/forum/showthread.php?t=329148 (the text below is my
tweaked version).

In a nutshell, you can export a pivot chart to a picture file or you can
export the summarised pivot TABLE that created the pivot chart. You cannot
export the chart to Excel as a chart in Excel requires actual data. So
options are limited.

====================
To export pivottables/pivotcharts to excel do the following:

1. Create your pivot chart .
2. On the top top left of the visual basic interface is a window
(ALT+F11). 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:
Code:

Sub ExportChart(Formname As String)

Dim ChForm As Object

DoCmd.OpenForm Formname, acFormPivotChart
Set ChForm = Forms.Item(Formname)
ChForm.PivotTable.Export "C:\Temp\Access.xls", plExportActionOpenInExcel
'note: xls NOT xlsx
'or the following if you want a jpeg output:
ChForm.ChartSpace.ExportPicture "C:\Temp\Access.jpeg", , 900, 500
DoCmd.Close acForm, Formname, acSaveNo
Set ChForm = Nothing

End Sub

3. Create a button on a new or existing form and set the target to the
above macro.

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

Hope this works for all those frustrated Access chart users out there.