none
How to refresh Excel Charts embedded in Powerpoint

    Question

  • I have multiple Excel grahs embedded into a PowerPoint file.  Each month, I update the graphs in Exel and would lke to updated grahs to appear when I open the PowerPoint presentation.

    How do I do this?

    Wednesday, November 13, 2013 10:51 PM

All replies

  • EXCEL & PowerPoint 2007

    You don't want to embed the charts into PP but link them as follows:-

    1. Click on the EXCEL chart to highlight it then:-

    CTRL-C

     - to copy it.

    2. Click in the PP slide where you want to link your chart then:-

    Home tab

    Clipboard group

    Click on the drop down arrow beneath:-

    Paste

     - then click on:-

    Paste Special . . .

    The:-

    Paste Special

     - window should open.

    3. In the:-

    Paste Special

     - window click on the radio button called:-

    Paste link

     - doing this should cause:-

    Microsoft Office Excel Chart Object

     - to appear highlighted in blue in the field called:-

    As:

    Click on:-

    OK

    4. You have now linked your EXCEL chart to your PP presentation.

    TO TEST THAT THE ABOVE HAS WORKED

    5. Close your PP presentation.

    6. Make a change to the EXCEL chart and save it.

    7. Open your PP presentation again.

    When you do this a warning should pop up.

    In that warning click on:-

    Update Links

    The change that you have just made in EXCEL should now be reflected in PP.

    Save the change to PP.

    Thursday, November 14, 2013 6:22 AM
  • Hi,

    Your require could be done via Paste Special and insert the Excel file as an object.

    Paste Special: Copy the Excel grahs > Paste to PowerPoint>Paste Special>Paste link

    Insert the Excel file as an object:Click insert tab>Object>Create from file (Note: check the link).

    Then you could update the Excel file and will also update in PowerPoint.

    Regards,

    George Zhao
    TechNet Community Support

    Thursday, November 14, 2013 7:12 AM
    Moderator
  • That is how I added the charts to the file.  However, I do not get the warning message.  In addition, when I click on the chart in PP, then select the Design tab (under Chart Tools), then refresh data, Excel opens.  However the source file in Excel DOES NOT open.  When I try to close Excel, I get a waring screen asking if I want to save changes to the source file (which I cannot see)

    Also, I have Office 2010

    • Edited by MAJustice Thursday, November 14, 2013 1:50 PM
    Thursday, November 14, 2013 1:41 PM
  • Between my point number 6 and my point number 7 leave EXCEL open (don't close it).

    When you open the PP presentation you should get the warning at this point (I do and it works OK here).

    Does it work now?

    I don't need to do the clicking on the chart (and the subsequent steps) that you mention.  

    You say that you cannot see the source file but your source file is EXCEL isn't it? (so how can you not see it?).

    Thursday, November 14, 2013 2:40 PM
  • This is a process that repeats monthly, so I have to leave both applications until I need to refresh them again the next month.

    At any rate, I currenly have both open, and am making changes to the Excel charts, and it does not change the data in PP.  If I click on the "refresh data" button, it tries to open another version of the file (and gives me an error message.  For that reason, I suspect the problem has to do with the file paths.

    The only solution I see is to delete the existing linked graph and replace with the current one.  To me, that defeats the purpose.

    The source file is defenitely Excel.  I don't know how I cannot see it.

    Thursday, November 14, 2013 3:13 PM
  • Are the files on a network by any chance?

    If they are what about saving them locally, running my procedure and then save back up to the network drive.

    Thursday, November 14, 2013 3:40 PM
  • They are both on my hard drive.
    Thursday, November 14, 2013 3:57 PM
  • I don't know what else to suggest then; sorry.
    Thursday, November 14, 2013 4:16 PM
  • Hi,

    Since you provided more imformation, I think you did not want to re-create the Excel file and PowerPoint file.

    But I suppose the issue may be cuased by the two files which there something wrong in them. Thus, could you give me the whole error message when you refresh the data?

    We may be force on the troubleshooting with the two files.

    Regards,

    George Zhao
    TechNet Community Support

    Friday, November 15, 2013 1:11 AM
    Moderator
  • Thank you man,

    You can't imagine how your post helped me. It solved the biggest issue I was having at work :)

    You are the man! Thanks!

    Friday, November 28, 2014 7:59 AM
  • @ MAJustice (OP)

    Although not as nice as just having everything linked and updating automatically, you might want to consider this fairly automated alternative.

    My situation is slightly different in that we re-use source graphs (dynamic source range selection) instead of creating loads of graphs in the source workbook. The code below will take the selected graph and the selected image in powerpoint, and copy/resize a new version from Excel to PPT. Then we update the graph source, and paste in the next set of data onto a different PPT slide.

    You could modify this by naming each of your images in PPT with a unique name, and naming each Excel graph with a correlated name, then instead of using the selected graph(s) and image(s), just cycle through every graph, look in the active PPT for a correspondingly named image, then replace using the core part of this code.

    Just an alternative to consider, if you can't get the linking to work.

    (be sure to set a reference to PPT in VBA)

    Sub ExcelChartReplacesExistingPowerPointChartAsPicture()

    'Credit to Jon Peltier for the initial code
    'http://peltiertech.com/Excel/XL_PPT.html
    'http://www.pptfaq.com/index.html
    'No warranty expressed or implied

        Dim PPApp As PowerPoint.Application
        Dim PPPres As PowerPoint.Presentation
        Dim PPSlide As PowerPoint.Slide
        Dim PPO As PowerPoint.ShapeRange
       
        ' Reference instance of PowerPoint
        On Error Resume Next
        ' Check whether PowerPoint is running
        Set PPApp = GetObject(, "PowerPoint.Application")
        If PPApp Is Nothing Then
            MsgBox "You need to have powerpoint open and an object selected to use this macro"
            Exit Sub
        End If
        On Error GoTo 0

        ' Reference presentation and slide
        On Error Resume Next
        If PPApp.Windows.Count > 0 Then
            ' There is at least one presentation
            ' Use existing presentation
            Set PPPres = PPApp.ActivePresentation
            ' Use active slide
            Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
            Set PPO = PPPres.Slides(PPApp.ActiveWindow.Selection.ShapeRange(1))
            x = PPApp.ActiveWindow.Selection.ShapeRange(1).Name
            xx = PPApp.ActiveWindow.Selection.ShapeRange(1).Left
            xy = PPApp.ActiveWindow.Selection.ShapeRange(1).Top
            xh = PPApp.ActiveWindow.Selection.ShapeRange(1).Height
            xw = PPApp.ActiveWindow.Selection.ShapeRange(1).Width
            PPApp.ActiveWindow.Selection.ShapeRange(1).Delete
            'Debug.Print x
        End If
        On Error GoTo 0

        On Error GoTo Whoops
       
        ' Copy the range as a picture
        Excel.Selection.Parent.CopyPicture xlScreen, xlPicture
       
        ' Paste the range
        PPSlide.Shapes.Paste.Select
       
        'Turn off aspect ratio
        Dim oSh As PowerPoint.Shape
        With PPApp.ActiveWindow.Selection
        For Each oSh In .ShapeRange
           oSh.LockAspectRatio = False
        Next
        End With
       
        ' Position pasted chart
        PPApp.ActiveWindow.Selection.ShapeRange.Left = xx
        PPApp.ActiveWindow.Selection.ShapeRange.Top = xy
        PPApp.ActiveWindow.Selection.ShapeRange.Height = xh
        PPApp.ActiveWindow.Selection.ShapeRange.Width = xw
        PPApp.ActiveWindow.Selection.ShapeRange.ZOrder (msoSendToBack)
       
        ' Clean up
        Set PPSlide = Nothing
        Set PPPres = Nothing
        Set PPApp = Nothing

        Exit Sub
       
    Whoops:
        MsgBox "An error occurred; the transfer was not successful. Please recheck your selections in both PowerPoint and Excel and try again.", , "Error copying Excel Chart"

    End Sub

    Tuesday, December 02, 2014 12:10 AM