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