How to update excel embedded charts in powerpoint?

Question

I have 30 charts that were created from excel and were pasted onto powerpoint slides. Every month, I have to update these 30 embedded charts by manually clicking on the charts and edit.

I am aware there is an option to use paste special, so that the data in the charts can be updated automatically by clicking the update links. However, my charts needs to be edited by some users. Paste special option does not allow users to edit the charts. Hence, I am unable to use this paste special option.

I think the solution lies in writing a vba in powerpoint. Can any expert here offer to write this vba code to allow all the charts to be updated in powerpoint? I am currently using powerpoint 2007. Your assistance is greatly appreciated.

brettdj · Accepted Answer · 2015-09-02T15:09:55.527

16

If you need to edit the charts then clearly you will either need to edit the underlying Excel files, or be able to edit in PowerPoint

As you are using PowerPoint2007 which provides full Excel support (unlike PowerPoint 2003 which has a datasheet) I would

Part 1

Link your Excel file data to the Excel data underneath each chart
Provide the ability to either use that data directly, or over-ride it with user data

Sample

This gives you a flexible solution, except that Excel underlying each chart cannot be updated automatically via a PowerPoint menu Update Links command.

Part 2

You can use the code below to test each whether each shape on each slide has a chart. If so this code will update the first Excel link in the Excel file underneath the chart (this part can be tweaked to handle multiple links)

    Sub ChangeChartData()

    Dim pptChart As Chart
    Dim pptChartData As ChartData
    Dim pptWorkbook As Object
    Dim sld As Slide
    Dim shp As Shape

    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes
            If shp.HasChart Then
                Set pptChart = shp.Chart
                Set pptChartData = pptChart.ChartData
                pptChartData.Activate
                Set pptWorkbook = pptChartData.Workbook
                On Error Resume Next
                'update first link
                pptWorkbook.UpdateLink pptWorkbook.LinkSources(1)
                On Error GoTo 0
                pptWorkbook.Close True
            End If
        Next
    Next

    Set pptWorkbook = Nothing
    Set pptChartData = Nothing
    Set pptChart = Nothing

End Sub

edited Sep 02 '15 at 15:09

answered Feb 09 '12 at 11:12

brettdj

54,857
16
114
177

Thanks for the prompt reply. I had copy and paste this vba in powerpoint and this vba is able to run. However, I am getting a pop up menu asking to update the file in my documents folder. My source i.e excel file is located in my desktop folder. How do i remove the pop up menu. Many thanks for this vba. – user1199080 Feb 10 '12 at 06:09
1

Thanks. I make a slight modification to the vba code and now its updates perfectly. This code is a life saver. Thanks again. – user1199080 Feb 15 '12 at 05:32
1

This is a great answer. Can this also break links? – mooseman Aug 24 '12 at 16:06
@mooseman yes, rather tha `UpdateLink` the code could be used to break links. If you like posted a question and I will update for it. thx for the compliment – brettdj Aug 25 '12 at 07:25
How do you create the linked data in part 1? – Nicolai Lissau Jun 01 '14 at 17:05
1

Didn't you mean to write 'xlWorkbook' instead of 'pptWorkbook' (the latter was never defined)? – AnyOneElse Sep 02 '15 at 11:07
Hi, is there a possibility to update the chart area, too? If I edit in the excel the chart by adding new rows to it, that the script also updates the new rows in the power point? – baam Aug 21 '18 at 07:40

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

2

This is fairly easily accomplished, without any VBA code required.

Click the "Office Button", select "Edit Links to File" (it's beneath "Run Compatibility Check", you will have to scroll down to see that last option):
Select all embedded charts (referred to here as "links"), click "Update Now":

You can use the same window to break links, as well as to change the source file for any given link.

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 21 '12 at 19:58

eykanal

26,437
19
82
113

1

This can be used if PPT is linked directly to Excel, but not if the linked Excel files sit underneath the charts – brettdj Feb 19 '13 at 07:40

How to update excel embedded charts in powerpoint?

2 Answers2

Linked