vba - 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.

Answer 1

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

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

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