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.
dougfosterNYCAsked:
Who is Participating?
 
CSLARSENConnect With a Mentor Commented:
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.
Cheers
cslarsen
0
 
CSLARSENCommented:
Hi,

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

cheers cslarsen
0
 
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.....


0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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.  

HELP!!!!!

    Set oPPChart = oPPTSlide.Shapes("Chart 38").Chart
    Set oPPSheet = oPPChart.ChartData
    Set oPPWB = oPPSheet.Workbook
    Set oPPWS = oPPWB.Worksheets(1)
0
 
CSLARSENCommented:
Hi,
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:

http://www.thecodecage.com/forumz/powerpoint-formatting/155839-new-powerpoint-2007-how-use-macros-move-picture.html

http://support.microsoft.com/kb/168649


Cheers
cslarsen
0
 
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.....


0
 
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?  
0
 
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)

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

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.