• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3838
  • Last Modified:

Referencing Powerpoint graphs using VB in an Excel macro

Hey all!

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?  

Cheers

Jack.

Sub UpdateGraph()
   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.
'
   rngNewRange.Select
   rngNewRange.Copy
'
' On slide one of Presentation1.ppt, loop through each shape.
'
   With oPPTApp.ActivePresentation.Slides(3)
      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("A1").Paste False

            End If

         End If
        Next oPPTShape
    End With
End Sub
0
skinnyl
Asked:
skinnyl
  • 2
  • 2
1 Solution
 
harfangCommented:
It looks as though you are simply finding existing chart objects and updating their datasheets. In that case, you can give meaningful names to them and refer to them by name and not by number. From the immediate pane of PowerPoint's VB, try:

    ? ActivePresentation.Slides(3).Shapes(4).Name

It might say "Object 7". This is the (unique) name of that shape. To make it more readable, you can rename it:

    ActivePresentation.Slides(3).Shapes(4).Name = "Overall Pie Chart"

Then you can directly use that name in your code:

    ' get the pie chart
    Set oGraph = oPPTApp.ActivePresentation.Slides(3) _
        .Shapes("Overall Pie Chart").OLEFormat.Object

Note that you still need to know the slide number or name ("Slide 3"). You can likewise rename your slides, in case you later reorganize them. From Powerpoint's immediate pane:

    ActivePresentation.Slides(3).Name = "Overview"

Then, you can use:

    ' get the "overall pie chart" on the "overview" slide:
    Set oGraph = oPPTApp.ActivePresentation.Slides("Overview") _
        .Shapes("Overall Pie Chart").OLEFormat.Object

This will still work if you insert a slide before this one or if you remove another chart from that slide. The same syntax can be used to refer to any number of charts on the same slide or on different slides.

Finally, you should probably add the library "Microsoft Graph ?.?? Object Library" in (Tools | References) so that you can write:

    Dim oGraph As Graph.Chart

This enables early binding and might make your programming a little easier.

Does that help?
(°v°)
0
 
skinnylAuthor Commented:
thanks for the reply!

Yes, I'm not trying to create any new charts, just update a 'template' if you will.

I see what your saying about the names, make good sense!

How to i discover the unique number of the shape (which i assume is a chart?) in order to assign it with a name? Also, i'm doing this from Excel in a macro. Powerpoint doesn't get a look in apart from being told what to do by Excel.

I understand that 'Shapes(4)' refers to the chart, just wonder where 4 comes from?

Thanks again

Jack.
0
 
harfangCommented:
I really just played with it:

    ? ActivePresentation.Slides(3).Shapes.Count
    ? ActivePresentation.Slides(3).Shapes(1).Name

etc.

You can also record a macro in Powerpoint where you select a chart and change something. Reading the macro will show the current name of the shape, which is just as useful as the number.

Note: don't forget to delete the module when done, or record the macro in a copy of your presentation.

Your own code can be used as well. Examine the shapes in turn with something like:

    ? ActivePresentation.Slides(3).Shapes(7).OLEFormat.ProgId

At least you will know it's a chart, even if you don't know which one.

Good luck!
(°v°)
0
 
GlennaShawCommented:
To use VB to name shapes:
http://www.tek-tips.com/faqs.cfm?fid=1698
And this one might help, it shows how to cycles through the shapes on a slide determining which is a graph object:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;qQ267974
0
 
skinnylAuthor Commented:
Sorry about the delay, got a bit sidetracked!

Cheers!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now