How to update excel embedded charts in powerpoint?
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
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
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.