I'm trying to automate the creation of a PowerPoint deck that I create a few times a week. I use a program called Alteryx to update the embedded excel files for all my charts in PowerPoint and that part works great. The problem that I have is that once I open PowerPoint, all the charts look the same as they originally were. It's only when I click edit data, that PowerPoint seems to read the excel changes and updates the chart. I have over 50 charts that I need to update, and clicking edit data on each one is very time consuming.
Is there a macro that can be created that will refresh all charts in my deck?
Thank you in advance for your time!
Update 1
I have tried using the following code from another post, but it results in 50 open workbooks.
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
I tried using the following code to open and close all the embedded workbooks, but it results in an error at this part "ActiveWindow.View.GotoSlide s.Slideindex"
Sub refreshchart()
Dim ppApp As PowerPoint.Application, sld As Slide
Dim s As PowerPoint.Shape
Dim gChart As Chart, i As Integer
ppApp.Visible = True
i = 3
Set sld = ActivePresentation.Slides(i)
sld.Select
For Each s In ActivePresentation.Slides(i)
If s.Type = msoEmbeddedOLEObject Then
s.Select 'select the object
s.OLEFormat.Activate 'Activate it (like 2x click))
ActiveWindow.Selection.Unselect 'To let it close
ActiveWindow.View.GotoSlide s.Slideindex 'make current slide active
End If
Next s
End Sub
A macro that opens the workbooks to refresh the charts and automatically closes them would be great. Thank you!
