Hi,
I've done a fair bit of research and it looks like there isn't a direct way to have a PP macro run when it is opened. When you add a PivotTable chart into a presentation from an exterior Excel document, it will live update if the Excel document PivotTable changes. Once you close the PowerPoint presentation, the link stops working even if you set it up in PowerPoint to automatically update. This is all done without VBA and just normal PP settings.
Since it doesn't update after the re-open, I found the following code that works. It will automatically re-connect the two files together and allow the pivot chart to update live again.
Is there a way with both files loaded to SharePoint or other method that can be used so the pivot chart is updated live in the PP file?
Also, if I embed a PivotTable, it won't update if the source in the Excel document updates. Is there a way to allow it to auto update the filters and sorts?
Thanks for the help.
Mike
I've done a fair bit of research and it looks like there isn't a direct way to have a PP macro run when it is opened. When you add a PivotTable chart into a presentation from an exterior Excel document, it will live update if the Excel document PivotTable changes. Once you close the PowerPoint presentation, the link stops working even if you set it up in PowerPoint to automatically update. This is all done without VBA and just normal PP settings.
Since it doesn't update after the re-open, I found the following code that works. It will automatically re-connect the two files together and allow the pivot chart to update live again.
Is there a way with both files loaded to SharePoint or other method that can be used so the pivot chart is updated live in the PP file?
Also, if I embed a PivotTable, it won't update if the source in the Excel document updates. Is there a way to allow it to auto update the filters and sorts?
Code:
Sub test()
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
shp.Chart.Refresh
On Error Resume Next
On Error GoTo 0
End If
Next
Next
Set pptWorkbook = Nothing
Set pptChartData = Nothing
Set pptChart = Nothing
End Sub
Thanks for the help.
Mike