Wednesday, March 28, 2007

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)



' update to keep changes





' tidy up objects

Set rngData = Nothing

Set objGraph = Nothing

Set objDataSheet = Nothing

Set objPrs = Nothing

Set objPPT = Nothing

End Sub

No comments: