Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    PPT and MS Graph (Excel and PPT XP)

    I am trying to refresh data in a PPT file that has MS Graphs on each slide. I have the data laid out just like it is in the MS Graphs within ppt. I am trying to make a macro to open each OLE graph in ppt and update the datasheet with numbers i have in an excel worksheet.

    I realize i can link between excel and ppt or create an excel object within ppt but the file become too large and my client doesn't like the "linking" feature. My client is used to using the MS graph function in powerpoint but all the graphs change each month with new data.

    Does anyone know of a utility that will do this or can someone start me out with a macro. I can't figure out how the MS Graphs are "named" to call from vba code. Thank you for the help.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PPT and MS Graph (Excel and PPT XP)

    I'll start with the easy part. As you said, you want to give the charts in your presentation a name so that you can find them in your excel macro. The easiest thing to do is to give the shape that contains the chart a name. Here is a simple macro to do this. I have also attached a presentation with the macro and two already-named sales charts for my new nationwide landscaping business. You will need to download this presentation to run the Excel macro in the next post.
    <pre>Option Explicit

    Sub NameSelection()
    On Error Resume Next ' Ignore cancel
    With ActiveWindow.Selection.ShapeRange
    .Name = InputBox("New Name", "Rename", .Name)
    End With
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PPT and MS Graph (Excel and PPT XP)

    Now the fun begins. I created an Excel worksheet with a named range, Updates. Updates is a list of names. Each name is both the name of a shape in the presentation that contains an MSgraph chart and also a named range on the worksheet that contains the new data. I have attached the worksheet and macro, but here is the source. You can also change the chart title and axis title, etc.HTH --Sam
    <pre>Option Explicit

    Sub updateSales()
    ' Be sure to use the Tools --> References menu to add
    ' both the Microsoft PowerPoint objects and
    ' the Microsoft Graph objects
    Dim ppApp As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
    Dim ppSlide As PowerPoint.Slide
    Dim ppShape As PowerPoint.Shape
    Dim gApp As Graph.Application
    Dim c As Range, src As Range, iRow As Long, iCol As Long

    Set ppApp = New PowerPoint.Application
    ppApp.Visible = msoCTrue
    Set ppPres = ppApp.Presentations.Open(Excel.Application.ActiveW orkbook.Path & "Sales.ppt")
    For Each c In Excel.Application.Names("Updates").RefersToRange.C ells
    Set src = Excel.Application.Names(c.Text).RefersToRange
    ' Find PP chart
    For Each ppSlide In ppPres.Slides
    For Each ppShape In ppSlide.Shapes
    If ppShape.Name = c.Text Then
    ' Update the data
    Set gApp = ppShape.OLEFormat.Object.Application
    With gApp.DataSheet
    .Cells.Clear
    For iRow = 1 To src.Rows.Count
    For iCol = 1 To src.Columns.Count
    .Cells(iRow, iCol) = src.Cells(iRow, iCol)
    Next iCol
    Next iRow
    End With
    gApp.Update
    gApp.Quit
    Set gApp = Nothing
    End If
    Next ppShape
    Next ppSlide
    Next c
    Set ppShape = Nothing
    Set ppSlide = Nothing
    Set ppPres = Nothing
    Set ppApp = Nothing
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: PPT and MS Graph (Excel and PPT XP)

    Sammy,

    Is this going to work only with Office XP? I tried it on 2000 and didn't get anything. Checked the settings under Tools | References when in Excel and it showed MISSING for both MS PPT 10 object library and MS Graph 10 object library. I know 10 is for XP.

    Thanks.

    Fred

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PPT and MS Graph (Excel and PPT XP)

    How about this?
    - Select Tools | References...
    - Clear the check boxes for the MISSING references.
    - Click OK.
    - Select Tools | References... again.
    - Set references to the Microsoft PowerPoint 9.0 Object Library and Microsoft Graph 9.0 Object Library.
    - Click OK.
    I don't see anything in the code that is specific to the XP versions.

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: PPT and MS Graph (Excel and PPT XP)

    Hans,

    THanks for helping out. Got some things taken care of but can't get it all working.

    Unchecked the Version 10 library references for both ppt and graph. Loaded Graph from CD (not loaded on initial install) and was able to check the libraries for the respective Version 9 ppt and Graph. Version 10 is now wiped out from the library references.

    Then went to run the updateSales macro from excel. Even with ppt closed, I could see ppt open and the Sales.ppt open. Then it stops. If I try to run the nameSelection macro from within ppt, nothing happens. I had the impression that I would be prompted for which of 2 sets of into I wanted to use.

    What am I doing wrong?

    Fred

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PPT and MS Graph (Excel and PPT XP)

    If you followed Hans's instructions, then it should work. It leaves the presentation on slide one. Are you sure that the presentation didn't get updated with the new data from Excel? Your first running scenario is correct. It does not check to see if the presentation is open, it expects it to be closed and in the same directory as the worksheet.

    I do not have an Office 2K machine at this location, but I will eventually be at my main plant and will check it out. I suspect that it is working so well that you didn't notice that the data changed! <img src=/S/wink.gif border=0 alt=wink width=15 height=15> HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: PPT and MS Graph (Excel and PPT XP)

    Sammy,

    Your suspicions were correct - it worked so well that it wasn't noticed. I actually spent about 1/2 hr this morning on this. I made a copy of ppt and compared the before and after. That's when I noticed the difference. Before doing that, I would run Excel, see the ppt pres open, and not see any difference. But when I closed the ppt, it asked if I wanted to save the changes. After a few times clicking No, I thought about making the copy and doing the compare.

    It also looked like the 3D bars in one of the graphs was bigger after the Excel macro ran than before. Why is that? Certainly there was a change in scale but I didn't think that would change the thickness of the bars.

    Also, what does the ppt macro do? I didn't understand it's purpose.

    Speaking of the ppt macro, how come when Excel opens the ppt pres, I don't get the macro warning? My security level is set to medium and I do get the warning when I open the Excel file. This seems like a major security breach to me, although, in this case, the ppt macro isn't doing anything.

    Lastly, I must be forgetting something but how did the graph objects get named in ppt? I couldn't find anything in ppt that lets me name them (unless you inserted the graph objects via VBA) or even anything in ppt where I could find out what the existing object names are. I saw the objects' names used in ppt by stepping thru the Excel code and looking at the names as each object on each slide was checked against the Excel ranges.

    Thanks.

    Fred

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: PPT and MS Graph (Excel and PPT XP)

    > Your suspicions were correct
    Great

    > It also looked like the 3D bars in one of the graphs was bigger
    Not sure. MS Graph has a mind of it's own. If it bothers your user, then he/she should be linking them to Excel.

    > Also, what does the ppt macro do?
    It allows you to name the shape that contains the MS Graph, so Excel can find it. The macro should be deleted after you have used it to setup your presentation.

    > how come when Excel opens the ppt pres, I don't get the macro warning?
    I noticed that, seems like a security breach to me.

    > how did the graph objects get named in ppt?
    With the PPT macro. See above and earlier post.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: PPT and MS Graph (Excel and PPT XP)

    Sammy,

    >Not sure. MS Graph has a mind of it's own. If it bothers your user, then he/she should be linking them to Excel.

    I'm not the original poster who was trying to satisfy a user. I'm in it just for the education. So thanks for that.

    >I noticed that, seems like a security breach to me.

    Another one? I just loaded the ppt file directly and got the macro warning. It seems if you go thru another app, like Excel in this case, you get around the warning. Hmmm. Where does one register security breaches?

    As to the naming, I guess nothing was happening until I selected an object. Now I see how that works.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •