Modify Powerpoint Charts via Excel's macro

Status : Not Proven

Source : http://support.microsoft.com/kb/267974

Sub UpdateGraph()

   Dim oPPTApp As PowerPoint.Application

   Dim oPPTShape As PowerPoint.Shape

   Dim rngNewRange As Excel.Range

   Dim oGraph As Object

'

' Set oPPTApp to PowerPoint by creating a new instance of PowerPoint.

' If PowerPoint is already open, you would instead use the GetObject

' method instead.

'   

   Set oPPTApp = CreateObject("PowerPoint.Application")

'

' Set PowerPoint to be Visible.

'

   oPPTApp.Visible = msoTrue

'

' Open Presentation1.ppt from My Documents.

'

   oPPTApp.Presentations.Open "C:\My Documents\Presentation1.ppt"

'

' Set rngNewRange to the collection of cells in the active Excel

' workbook and active sheet.

'

   Set rngNewRange = ActiveSheet.Range("A1:F4")

'

' Select the range then copy it.

'

   rngNewRange.Select

   rngNewRange.Copy

'

' On slide one of Presentation1.ppt, loop through each shape.

'   

   With oPPTApp.ActivePresentation.Slides(1)

      For Each oPPTShape in .Shapes

'

' Check to see whether shape is an OLE object.

'

         If oPPTShape.Type = msoEmbeddedOLEObject Then

'

' Check to see whether OLE object is a Graph 2000 object. The ProgID

' is case sensitive.

'

            If oPPTShape.OLEFormat.ProgID = "MSGraph.Chart.8" Then

'

' Set oGraph to the Graph object on the slide.

'

               Set oGraph = oPPTShape.OLEFormat.Object

'

' Paste the cell range into the upper leftmost cell of the graph

' datasheet. This position is designated "00" (two zeros). To designate

' a range to start in the second row, first column, you would use "01".

' Likewise first row, second column is "A0". This will also link the

' datasheet to the Excel Workbook cell range. If you do not want to

' link to the Workbook, just omit the word "True". The default

' choice for the Paste method is "False".

'

               oGraph.Application.DataSheet.Range("00").Paste True



            End If



         End If

'

' Select the next shape on the slide.

'

      Next oPPTShape

   End With

End Sub

Powered by ScribeFire.