Le_Nuss
02-18-2015, 08:25 AM
Hi,
I use PowerPoint 2013. I would like to automatically update data for my graphs using embedded excel files. The embedded excel files contain formulas that are linked to another excel file. After having updated my main excel file, I can update the formulas in my PowerPoint using the following code (I found it somewhere and it works perfectly):
Sub ChangeChartData()
Dim pptChart As Chart
Dim pptChartData As ChartData
Dim xlWorkbook 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
Nevertheless, the macro keeps formulas in my data tables whereas I'd like it to paste only values after updating (so that my updated PowerPoint file becomes independent from the excel file called by the formulas). I tried to include this code in the macro but it doesn't work:
ChartData.Select
Selection.Copy
PasteSpecial Paste:=xlPasteValues
Would you please help me with that and tell me how to add a 'copy & paste values' feature in the macro?
Thanks a lot!
I use PowerPoint 2013. I would like to automatically update data for my graphs using embedded excel files. The embedded excel files contain formulas that are linked to another excel file. After having updated my main excel file, I can update the formulas in my PowerPoint using the following code (I found it somewhere and it works perfectly):
Sub ChangeChartData()
Dim pptChart As Chart
Dim pptChartData As ChartData
Dim xlWorkbook 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
Nevertheless, the macro keeps formulas in my data tables whereas I'd like it to paste only values after updating (so that my updated PowerPoint file becomes independent from the excel file called by the formulas). I tried to include this code in the macro but it doesn't work:
ChartData.Select
Selection.Copy
PasteSpecial Paste:=xlPasteValues
Would you please help me with that and tell me how to add a 'copy & paste values' feature in the macro?
Thanks a lot!