Microsoft Acess Charts dead-end.
November 23, 2010 1:04 AM   Subscribe

Microsoft Access 2007 - how on earth do I cut / copy / export / do anything with my Pivot Chart, besides print it?

I've been putting together an Access database for my wife's work - I have extensive experience with RDMS and SQL, so I figured I could wrangle Access 2007 into shape and make it produce some reports and charts on demand. I've got it all working beautifully, except that I've generated a Pivot Chart for a query, there appears to be no way to get it into any other piece of software. There's no way to copy/paste it. All the export options (export to Word, export to Excel etc.) end up giving producing the output of the query, not the chart.

Surely there's a way to do this besides print-screen/paste, which wouldn't really satisfy the people this is intended for. If anyone has any creative solutions, I would be most thankful.
posted by Jimbob to Computers & Internet (12 answers total) 1 user marked this as a favorite
Can you copy the data, value paste in another sheet, then copy that data into whatever program you need?
posted by armage at 2:09 AM on November 23, 2010

Ack, sorry, ignore my answer. I misread the question.
posted by armage at 2:10 AM on November 23, 2010

From a little googling, it seems you can export the objects as gif or jpeg using VBA, but I don't have access 2007 here. I've got 2010 at work, but would have to wait until tomorrow to have a bash

That said: one thing I might try would be to access the access table/query/view/whatever from excel, and generate the pivotchart there, which I suspect may be easier to export.
posted by pompomtom at 2:37 AM on November 23, 2010

Response by poster: Yeah, it would be easier to export from Excel, I know I've copied-and-pasted Pivot Charts from there before...I'm just not entirely sure how to access the query efficiently from within Excel. The requirements for the database were for it to be a self-contained Access file, where someone can open it, add some data, tick some boxes, select something from a list, and a query is run that spits out a chart for them... very frustrating, but if there's some VBA code out there that will put a GIF of the chart onto the clipboard, that would be progress.
posted by Jimbob at 2:54 AM on November 23, 2010

In the most recent version of Microsoft Windows (I *think* that's 2007...), you can export to other formats with a right click. Don't remember the exact phrasing, but go to the database objects window in Access (where they show all the forms & queries & such), and right click on the query. Something appropriate should be on the list of options. One of the sub-options will get you to Excel.
posted by Ys at 3:47 AM on November 23, 2010

Response by poster: Ys, that just exports the data as text; there's no option to force it to export the graphics.
posted by Jimbob at 11:32 AM on November 23, 2010

Are the graphics linked in (you told Access where the graphic file was, either on the internet or on your computer), or are they pasted in (you saved them directly into Access)? If they were linked in, is it exporting the link? Because Excel can usually work with a link, even if you have to go back and activate it. Beyond that, I'm afraid I don't have any more bright ideas.
posted by Ys at 1:15 PM on November 23, 2010

OK, access help and a bit of googling lead me to believe that if you can set a reference in the VBE to "Microsoft Office Web Components", you can then access the ChartSpace form object , which should allow you to do something like:

Application.Forms("formname").ChartSpace.ExportPicture "C:\examplefilename.gif", , 1024, 1024

...but I'll be buggered if I can find the library to reference anywhere in my setup (Access 2010, Win7), so I can't test this.

Alternatively, you could 'print' the chart to an XPS file using the print dialog, and maybe find something to convert that to a sane file format.
posted by pompomtom at 3:57 PM on November 23, 2010

Response by poster: Thanks pompomtom, I'll investigate doing that in VB. I might end up simply telling them they'll have to print to an XPS or PDF, if that doesn't work.
posted by Jimbob at 4:22 PM on November 23, 2010

Best answer: OK, got it!

Had to get owc10.dll from here, and set a reference to it in the VBE. With that library, the chartspace objects become available, and that ExportPicture method works as above (the 1024s are the size of the resultant image. Also seems to be able to create PNG and JPG files by just changing the file extension.
posted by pompomtom at 4:23 PM on November 23, 2010

Response by poster: Okay, I'm half way there... The code fails, I think, because what I'm trying to save isn't an Access form,'s the output from an Access query, so when I try the code:
Application.Forms("TypeXServiceQuery").ChartSpace.ExportPicture "C:\examplefilename.gif", , 1024, 1024
I get the error:
Run-time error '2450': Database can't find the form "TypeXServiceQuery" referred to in a macro expression or Visual Basic Code.
I'm hopeless at and hate Visual Basic, so I'm probably not using the right terminology here, but there's no Application.Queries().Chartspace... function, or anything like it, just one for "forms".

I thought about doing it self-referentially, ie.
But there's no "Me" in this context...I can't figure out how I attach VB code to a query!

Seriously starting to wish I'd convinced them to do this crap in PHP/MySQL....
posted by Jimbob at 11:42 PM on November 23, 2010

Yeah, sorry, I ran into this. I'm guessing you've taken the query and are 'viewing as' a pivotchart. What you need to do is to create a Form, which just happens to take the form of a pivotchart based upon your query.

Again, I'm home now and can't confirm the exact button presses, but I did run into this when I was mucking about. I basically went 'new form'->'other-kind-of-form'->pivotchart.

Seriously starting to wish I'd convinced them to do this crap in PHP/MySQL....

An entirely sensible point of view. I'm becoming quite the my-office access guru this year, and it shits me to tears.
posted by pompomtom at 1:04 AM on November 24, 2010

« Older I'm a sole man   |   Massage talent Newer »
This thread is closed to new comments.