Office Development

Updating the Styles of Embedded Excel Tables in PowerPoint 2007/2010

David E. CraigLeave a comment

PowerPoint 2007/2010 are great with updating your presentation with new styles or themes. Excel Charts, SmartArt objects and shapes are all updated with a click of a button. However, you may find that Excel Charts embedded in the presentation are not updating. Here is some sample code to get these to update…

VBA:

Code Snippet
  1. Sub UpdateExcelTables()
  2.     Dim sld As Slide
  3.     Dim sha As Shape
  4.     For Each sld In ActivePresentation.Slides
  5.         For Each sha In sld.Shapes
  6.             If sha.Type = msoEmbeddedOLEObject Then
  7.                 If InStr(1, sha.OLEFormat.ProgID, "Excel") Then
  8.                     Dim xlApp  As Excel.Application
  9.                     sha.OLEFormat.Activate
  10.                     Set xlApp = sha.OLEFormat.Object.Parent
  11.                     xlApp.Workbooks(1).ApplyTheme _
  12.                         "C:\Program Files\Microsoft Office\" & _
  13.                         "Document Themes 12\Flow.thmx"
  14.                     xlApp.Quit
  15.                 End If
  16.             End If
  17.         Next
  18.     Next
  19. End Sub

C#:

Code Snippet
  1. foreach(PowerPoint.Slide sld in pres.Slides)  
  2. {
  3.     foreach (PowerPoint.Shape sha in sld.Shapes)  
  4.     {  
  5.         if (sha.Type == MsoShapeType.msoEmbeddedOLEObject  
  6.             && sha.OLEFormat.ProgID.Contains("Excel"))  
  7.         {  
  8.             sha.OLEFormat.Activate();  
  9.             object o = sha.OLEFormat.Object;  
  10.             // "Parent" property is not exposed,  
  11.             // but this is how we get reference to  
  12.             // the Excel Application  
  13.             Excel.Application xlApp =  
  14.                 (Excel.Application)o.GetType().InvokeMember  
  15.                 ("Parent",BindingFlags.GetProperty,  
  16.                     null, o, null);  
  17.             // specify the full path and filename  
  18.             // of the theme you wish to apply here  
  19.             xlApp.Workbooks[1].ApplyTheme(  
  20.                 @"C:\Program Files\Microsoft Office\"+  
  21.                 "Document Themes 12\Flow.thmx");  
  22.             xlApp.Quit();  
  23.         }    
  24.     }
  25. }

IMPORTANT NOTE:

If you look at the C# code we are doing something that may look odd:

  1. Excel.Application xlApp =  
  2.     (Excel.Application)o.GetType().InvokeMember  
  3.     ("Parent",BindingFlags.GetProperty,  
  4.      null, o, null);

This does the same thing as the VBA code:

  1. Set xlApp = sha.OLEFormat.Object.Parent

So, it turns out that the Office PIA’s do not implement all of the functionality you might see from VBA. The property “Parent” is still in the Object Model, it is just not exposed to the C# programmer. So what we are doing is Invoking that object we know is there.

This is sort of a neat workaround to those cases where you find you can do something in VBA, but you do not see it in the PIA.

Leave a Reply