Google Answers Logo
View Question
Q: Export MS Access Pivotchart ( Answered 5 out of 5 stars,   1 Comment )
Subject: Export MS Access Pivotchart
Category: Computers
Asked by: brettola-ga
List Price: $20.00
Posted: 05 Apr 2006 09:25 PDT
Expires: 05 May 2006 09:25 PDT
Question ID: 715733
I have MS Access queries and forms tied to those queries.  The forms
are set to be viewable only as PivotCharts.  These work fantastic and
are very helpful.  The problem is that they are so fantastic that
upper mgmt and clients are asking to receive a bundled report via
email containing about 15-20 of these Pivotcharts.

I cannot figure out how to get these pivotcharts out of MS access
while maintaining the pivotchart formatting - other than taking
screenshots one at a time and pasting into word or similar!

I know access fairly well.  I don't know VBA at all.  I found the
"answer" to this problem here:
I registered the dll it referenced.  It says to use this code:
Me.ChartSpace.ExportPicture "C:\yourdirectory\filename.gif", , 1024,

Great - how do I do that?  

My Pivotcharts are named "frm_dashboard1" through "frm_dashboard20".

So - the answer must provide detailed instructions:
1)how do I set up my access db to output these 20 pivotchart forms as
GIF, BMP, JPEG, pdf, anything that retains the formatting.
2)This must be done in a way that can be automated
3) details on the automation (macro, whatever) included.
Subject: Re: Export MS Access Pivotchart
Answered By: hammer-ga on 05 Apr 2006 13:06 PDT
Rated:5 out of 5 stars

1. Create a new Form.

2. Put a button on it.

3. Paste the following into the button's Click event. Note that no
lines should wrap. The Answer box may wrap lines.
*** NOTE: There is a line marked in the code which you should change
to a directory that exists on your computer. ****

' -----------------------------
Dim frm As Form
Dim cht As ChartSpace
Dim strForm As String
Dim strFile As String
Dim iLoop As Integer
Dim strDir As String

    ' Change this line to the directory where you want your pictures.
    ' Make sure you keep the trailing slash
    strDir = "D:\Jobs\Google\PivotChart\"

    For iLoop = 1 To 20
        strForm = "frm_dashboard" & CStr(iLoop)
        strFile = "dashboard" & CStr(iLoop) & ".gif"
        DoCmd.OpenForm strForm, acFormPivotChart
        Set frm = Application.Forms(strForm)
        Set cht = frm.ChartSpace
        cht.ExportPicture strDir & strFile, , 1024, 1024
        DoCmd.Close acForm, strForm
    Next iLoop
' -----------------------------

4. You may not have to create reference to the dll you mention above
at this point, because you say already have. If you have not, you will
need to do so.

5. Save the Form.

Now, when you click the button, it should create your gifs. You can
then attach the gifs to an email or whatever you need to do with them.

Good luck with your Access project!

- Hammer

Search strategy
Read article provided by customer. Created Access database and wrote/tested code
per customer specifications.
brettola-ga rated this answer:5 out of 5 stars
worked perfectly.  fast response.  most appreciated!

Subject: Re: Export MS Access Pivotchart
From: dkchoudhary-ga on 18 May 2006 10:47 PDT
It also helped me in exporting my pivot charts in the form of images.
Though I have another question. I have used multichart functionality
of the pivot chart. Hence I can see 6 different charts based on the
criteria. What I need to do now is to split these 6 chart so that I
can select top 5 groups for each chart. Can you please help me.


Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy