PowerPoint 2007 automate graph strategy

I am starting on a project to automate PowerPoint 2007 slides that have graphs.  I've done a similar project in 2003, which uses MS Excel as a base to push lots of data into a PowerPoint template, but the graphs are a totally new animal and aren't MS Graph objects any more.

I'm continually amazed how there seems to be no developer communities on PowerPoint or good documentation.  So I am struggling to find a good approach.  At the bottom of this post there is a quote from http://pptfaq.com/FAQ00889.htm, which seems to be the only place to find info on PowerPoint VBA.  The quote basically says I have NO access to the graphs, which is disturbing to think I may not have a solution.

So, what is the best approach?  Is there a way to access graphs in PowerPoint, despite the quote below, if I am opening an instance from Excel (where all the data are coming from)?  If not, can I set up all the graphs in Excel, modify them, then place them in PowerPoint?  Do I need to make sure that I and my client have installed Office 2007 with MSChart?  Has anybody done anything like this?  

I first need to find the best approach.  

------------------------------------------------------------ Quote from PPTFAQ --------------------
    If you need to automate charts in PowerPoint 2007, it'd be best if you change your name and move to another town where nobody knows that you do PowerPoint programming.

    The chart objects you get in PowerPoint 2007 when you add a new chart expose no methods or properties to VBA. In short, you can't do anything with them in code.

    When you install Office 2007, you have the option of including MSGraph, the application PowerPoint used for charts in previous versions. You'll want this if you need to work with charts from previous PowerPoint versions and/or to create new charts via automation.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


I would generate the charts in excel, copy them as pictures and automate generating the slides.

cheers cslarsen
dougfosterNYCAuthor Commented:
Thanks, I would like to do that also.  So far the best way to paste them into the slides is as enhanced metafiles.  

BUT, I need to locate them exactly.  They are going into an existing PP slideshow.  If I can't place them exactly in their place, it is not going to work for me.

The other way to do it, which actually may be better, is to have all the charts be linked to the spreadsheet and I just change the data in the sheet.  That is great, except PP 2007 is acting buggy and the graph keeps disappearing, either the entire graph becomes invisible or parts of it, like the labels.  Weird.

PowerPoint is a pain to automate.....

dougfosterNYCAuthor Commented:
Wow, it seems like nobody is doing real PowerPoint vba work.  What a bummer.  I found an article at http://blogs.msdn.com/excel/archive/2009/08/18/chart-object-model-in-word-powerpoint.aspx that explains how to get access to the charts data sheet, which is an excel workbook, worksheet.  But when I try to set the workbook object, it throws an error, saying

vba runtime error -2147467259
method 'Workbook' of object 'ChartData' failed

The code is below. It does make sense,  I guess, that I set the chartData object, which works, and then set the workbook and sheet.  But I'm unable to set the workbook.  


    Set oPPChart = oPPTSlide.Shapes("Chart 38").Chart
    Set oPPSheet = oPPChart.ChartData
    Set oPPWB = oPPSheet.Workbook
    Set oPPWS = oPPWB.Worksheets(1)
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

I made my own excel addin, which can move all charts as pictures to a dynamically generated powerpoint.
I took forever to figure out the best format, but I also concluded "enhanced metafiles" as the best format.

The linked version concept seems appealing, but I can only agree: it is unstable and you must be prepared to make heavy quality assurance.

Therefore I can recommend use the "enhanced metafiles" and move them around.

Some VBA PPT links:



dougfosterNYCAuthor Commented:
Ok, I also found that enhanced metafiles seem to retain the colors, resolution, etc. and hopefully will be acceptable.

But what is the best way to get the object into the slide?  I need to position it exactly.  Do I paste in the graph and then locate it?  Or can I have an object on the slide and then add a graph object to it?  

I'm amazed how there is no documentation on this, or at least how elusive it is.....

Yeah, I agree it is not meant to be....

I found the "paste in" and then position the chart as the most stable solution.
But principaly I dont think theres much difference to the other approach you mention, but you encounter other challenges as Zoom and/or crop if the sizes are not eaqual.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dougfosterNYCAuthor Commented:
Thanks.  Can you give me the approach (code hopefully) to paste the object in (which I can do), retain/obtain the object id, and then position it?  I can do the positioning once I have the id, but is there a way to paste it in and have the id?  
dougfosterNYCAuthor Commented:
Ok, I FINALLY figured out how to paste in a picture and keep track of the object, besides assuming there is no other and it is number 1, like all the other examples give.....

The trick is that pastespecial returns a shape range, not a shape, so the item(1) at the end returns the first item in the range.  At least I KNOW it will be the first item since I am pasting my object into the slide.

Set oPPTShape = oPPTSlide.Shapes.PasteSpecial(ppPasteEnhancedMetafile).Item(1)

dougfosterNYCAuthor Commented:
I still needed to figure out how to track the object, but this helped me define the best approach.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft PowerPoint

From novice to tech pro — start learning today.