Need to export a pivot chart view of a query

[AmandaM]

Hi -

I need to export the pivot chart view of a query to a word or excel document
so that it can be emailed or saved.

Any ideas?

 

[LeAnne]

Hi -

I need to export the pivot chart view of a query to a word or excel document
so that it can be emailed or saved.

Any ideas?

Several:

- Highlight all the fields/columns in your xtab query in Datasheet View
and Copy/Paste to Word or Excel

- OR select your xtab query in the Database Window, select File > Save
As/Export, click the "To an external file or database" radio button,
then choose the preferred file type & destination.

- OR select your xtab query in the Database Window and use the
OfficeLinks toolbar button to "Analyze it with MS Excel" or "Publish it
with MS Word".

- OR to email the output of the query directly, select your xtab query
in the Database Window, then select File > Send and follow the prompts.

That should get you started!

hth,

LeAnne

 

[AmandaM]

Thanks LeAnne - Unfortunately none of those options work, I still get the
datasheet pulling over rather than the chart.

 

[LeAnne]

Could you clarify what you have and what you are trying to do? Did you
create a crosstab query (sometimes called a pivot table) that you wish
to export to Excel? Or did you use ChartWizard to add a chart to an
Access form or report?

LeAnne

 

[LeAnne]

Could you clarify what you have and what you are trying to do? Did you
create a crosstab query (sometimes called a pivot table) that you wish
to export to Excel? Or did you use ChartWizard to add a chart to an
Access form or report?

PS. Forgot to include this part. You didn't say which version of
Access/Office you are working with. But if you are working with a Chart
object created in a From or Report in A97:

- Open the Form or Report containing the graph in Design View
- Right click on the Chart object and Select Chart Object > Edit
- Microsoft Graph should open 2 windows: a datasheet view of the
RecordSource data, and the chart itself
- From the menu bar select Edit > Copy Chart
- You can now paste the chart into Excel or Word as an EMBEDDED object.
See Excel Help for information about working with embedded charts. Or
you can Paste Special the graph as a .jpg or other picture format if you
prefer.

hth,

LeAnne

 

[AmandaM]

Hi LeAnne-

I'm working in Access 2003. I created a "select" query. When I had it
showing the data I wanted to chart, I went to >View>Pivot Chart View. In that
window I created the Pivot Chart and saved it so that each time I run this
query I can click over to pivot chart view to see the charted data. (which
ultimately I would like to export)I hope this makes more sense.

Thanks for your help.

 

[LeAnne]

Hi LeAnne-

I'm working in Access 2003. I created a "select" query. When I had it
showing the data I wanted to chart, I went to >View>Pivot Chart View. In that
window I created the Pivot Chart and saved it so that each time I run this
query I can click over to pivot chart view to see the charted data. (which
ultimately I would like to export)I hope this makes more sense.

Thanks for your help.

<wince> I'm afraid I'm not at ALL familiar with that functionality.
Anybody else knowledgeable in A03 want to take a crack at this?

Sorry,

LeAnne

 

[LeAnne]

Hi LeAnne-

I'm working in Access 2003. I created a "select" query. When I had it
showing the data I wanted to chart, I went to >View>Pivot Chart View. In that
window I created the Pivot Chart and saved it so that each time I run this
query I can click over to pivot chart view to see the charted data. (which
ultimately I would like to export)I hope this makes more sense.

Thanks for your help.

PS. Amanda, here's a link to an article I found at office.microsoft.com:

http://office.microsoft.com/en-us/assistance/HA010345791033.aspx

Scroll down to "Samples for PivotChart view" and click on "Export the
PivotChart View as an image." There's a snippet of VBA code that will
enable you to export the chart object as a .gif.

LeAnne

 

[AmandaM]

Thank you!!! I'm sure that will help...now I just have to get the code in
there. At least now I know it's possibble.

 

[Colin]

How hard does this need to be. I have the same problem and it would be great
to have a solution that doesn't require learning how to program in visual
basic. I have resorted to taking a screen capture (print screen button) and
pasting it to a word document (ctrl + V). There must be a better way.

 

[peggy]

still i do not succeed to do this, even with the code
Me.ChartSpace.ExportPicture "pathname\filename.gif"

i put the pivot chart in a subform and on the major form a button with the
code
Me.ChartSpace....

i gte the message :
"object closed or does not exist

what do i do wrong ?

 

[tekkag]

I just do screen print, then paste to paint and cut and copy what i need and
dont need, its the best way i know of.

 

[Bizarre]

I have the problem, I tried the solution given in the article but it doesn't
work. I wanna export the chartspace (from a userform) to a gif file. but I
get an error message
"class does not support automation or does not support expected interface"

my program is:
Dim stFileName As String
Dim owcChSpace As OWC11.ChartSpace

Set owcChSpace = Me.ChartSpace1

'Create the filename.
stFileName = _
Replace(CStr(Date), "-", "") & _
Replace(CStr(Time), ":", "") & ".gif"

With owcChSpace
'Temporarily hide the toolbar.
.DisplayToolbar = False
'Export to a standalone GIF-file.
.ExportPicture _
Filename:="c:\My Documents\" & stFileName, _
FilterName:="gif", _
Width:=700, _
Height:=440
'Display the toolbar again.
.DisplayToolbar = True
End With

MsgBox "Done!", vbOKOnly, "OWC Chart"

Set owcChSpace = Nothing

 

[sailcm]

Thanks LeAnne,
I got this to work. I read the rest of the postings where everyone had
trouble and thought I would share...

On the chart's On Double Click property, I put in the following code:

Private Sub Form_dblClick(Cancel As Integer)
Me.ChartSpace.ExportPicture "H:\My Documents\My Pictures\Name.gif", , 930, 720
MsgBox "done"
End Sub

When the chart is open, I can double click anywhere on the chart and the
..gif is created.
By the way, the .gif is clearer & less grainy than the "Ctrl+Print Screen
Paste" method when on powerpoint; and with a little trial and error found
that the 930x720 fits onto the ppt slide without resizing.

 

[pcorey]

I've read through the thread and I must be close but I'm still getting errors.

Using the On Double Click property written below I get the error "The
expression you entered refers to an object that is closed or doesn't exist."

Help! Why is it that this is so difficult to do and to find instructions
for? It's a great new feature but useless if it can't be exported.

 

[sailcm]

My company recently upgraded to access2003. Now the code that I posted
earlier doesn't work. It's driving me crazy...

 

[pcorey]

That's what I use as well - Access2003. I can't get anything to work! It's
incredibly frustrating. The best solution I've found is to save as a PDF and
then cut out the image from there to paste into Word/Powerpoint.

 

[makyland]

I know this post is a bit old... buy maybe I could be of help.

As far as I've found out the "exportpicture" method starts failing after the
upgrade of OWC10.dll. You can check your dll version in path:
C:\Program Files\Common Files\Microsoft Shared\Web Components\10

I've seen it starting to fail from version: 10.0.6765.0

If you're able to get a copy of a previous version of the dll (I can let you
know that version 10.0.5605.0 still works!) you can replace "the newest one"
by the one where the method actually works. (If you don't want to lose the
newest one just rename it and keep it safe...)

However, this is a bit "dirty" if you're trying to distribute the oldest dll
as part of your solution as installation package might not attempt to replace
a newer dll by an older one... and you might end up asking your users to
manually change the dlls... (which is not very ellegant though...). But if
you're using this functionality for yourself it will do!

I'm wondering if this issue has been solved with Access 2007, there is any
hotfix for this for Access 2003 or there is any way to make the method work
with the newer version of the dll.

Has anyone read any direct feedback from Microsoft?

Thanks in advance for any comments on this!

 

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