Modify Powerpoint Charts via Excel's macro

Status : Not Proven



It uses an existing powerpoint presentation with a graph on slide 2.

Sub Chart2PPT()

  Dim objPPT As Object

  Dim objPrs As Object

  Dim objGraph As Object

  Dim objDataSheet As Object

  Dim rngData As Range

  Dim intRow As Integer

  Dim intCol As Integer

 

   ' excel chart data

  Set rngData = Range("A1:B4")

   ' open powerpoint

  Set objPPT = CreateObject("Powerpoint.application")

  objPPT.Visible = True

   ' existing powerpoint pres

  objPPT.presentations.Open ThisWorkbook.Path & "\21113.ppt"

   ' chart on slide 2

  Set objPrs = objPPT.presentations(1).slides(2)

   ' pointer to graph

  Set objGraph = objPrs.Shapes(2).OLEFormat.Object.Application

   ' pointer to graphs data sheet

  Set objDataSheet = objGraph.Datasheet

   ' transfer data

  For intRow = 1 To rngData.Rows.Count

      For intCol = 1 To rngData.Columns.Count

          objDataSheet.Cells(intRow, intCol) = rngData.Cells(intRow, intCol)

      Next

  Next

   ' update to keep changes

  objGraph.Update

  objGraph.Quit

  objPPT.presentations(1).Save

  objPPT.Quit

 

   ' tidy up objects

  Set rngData = Nothing

  Set objGraph = Nothing

  Set objDataSheet = Nothing

  Set objPrs = Nothing

  Set objPPT = Nothing

End Sub