Referencing Powerpoint graphs using VB in an Excel macro
Posted on 2007-08-01
I'm using the below in a macro to automatically port data from excel into powerpoint. The code lies in the excel workbook.
It works well but i have got to the stage where there are multiple graphs on one slide and i'm not sure how to reference them. at the moment the below just uses the slide reference (oPPTApp.ActivePresentation.Slides(3)) to get the graph.
Question- is it possible to reference more than one graph using VB?
Dim oPPTApp As PowerPoint.Application
Dim oPPTShape As PowerPoint.Shape
Dim oPPTPres As PowerPoint.Presentation
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("E11:E17")
' Select the range then copy it.
' On slide one of Presentation1.ppt, loop through each shape.
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".