Need to export a pivot chart view of a query

[ccgreenman]

having problems with this

I'm running Access 2003 and have tried several different ideas I've found on the different forums.

I tried the above post puting in the code on the double click so that I could just export it from the chart.

I get the following error:

"MS Office Access can't find the macro 'private sub form_dblClick(Cancel As Integer)Me.'

I get the same type of error no matter what I try. It can't find the Macro Me.

I've tried this with both OWC10 and 11.

I don't know much about macros, and nothing about VB.

Please - someone help - there just has to be an easier way.

Thank you in advance for any help you can give me!

Corinne

 

[DawnTreader]

Hello

why not go about this another way. in excel on the data menu choose pivot
table or chart wizard. then use an external data source and point it to the
access database you are using. then point it to the query and create your
pivot table and chart in excel.

then not only do you have a pivot table and corresponding chart, but it is
"live" data, meaning that if you change something in the database, it changes
in your pivot table and chart with a simple refresh.

 

[makyland]

This can be an option when you're working "for yourself" and you can either
work with Excel or Access, but if you're designing an application only Access
based and you're not allowed to mess up opening any other Excel views you
might eventually need a procedure to save your pictures as images.

You do also have "live data" in your Access pivot chart and you might not
even need to click "refresh" button as the requery method of the query
recordsource does update data in your Access pivot chart. Also, Access pivot
charts allow more features when compared to Excel's (at least until 2003):
you have multichart, multiple series for scattered plots with approx.
equation and others...

I happened to find a way to programmatically make the last, newest OWC dll
work with this ExportPicture function, in case anyone is interested in.

Regards,

 

[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.