How to "Refresh Data" via VBA in Power Point?

Question

so far I have tried the Chart.Refresh and Chart.Update and also ChartData.UpdateLinks and neither work. My question is similar to this one only that this code did not work for my ppt How to update excel embedded charts in powerpoint?

If i could Record Macro like in Excel the steps would be:

1. Select Chart

2. Chart Tools > Refresh Data

This is code is what I have managed to write but it fails at "gChart.Application.RefreshData":

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
   Set gChart = s.OLEFormat.Object
   With gChart.Application

   gChart.Application.Refresh
   Set gChart = Nothing
   End If
  Next s

 End Sub

The Integer i is included to go from i=1 To 73, but as a test i am using Slide 3. Not all Slides have Charts but most of them have 4 Charts (65 out of 73).

Solution 1

I changed the code a little bit and with this little change, the refresh of the charts works again automatically.

Many times, if you share your excel ppt combo the links break and after restoring them the automated chart refresh doesn`t work.

With the downstanding macro the automated refresh will work again:

Sub REFRESH()

    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

The code below is in a macro in the Excel workbook which also contains the source data. Not sure if the code would be the same running it from PowerPoint. I simply open my Excel Workbook and then have it update the PowerPoint for me.

I have been looking forever to find an answer to this and finally managed to get it to work with a ton of reading and trial-and-error. My problem was that I have a PowerPoint with a lot of graphs that were created with CTRL+C and CTRL+V, so none of them are linked. This is how I got it to work:

Dim myPresentation As PowerPoint.Presentation
Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim myChart As PowerPoint.Chart

For Each sld In myPresentation.Slides
    For Each shp In sld.Shapes
        If shp.HasChart Then
            Set myChart = shp.Chart
            myChart.ChartData.Activate
            myChart.Refresh
        End If
    Next
Next

I don't know if there is unnecessary code in there but I am just happy that I finally got it to work so I'm not touching it anymore.

Solution 2

This code worked. But it works only if both files are open (the excel if its only one): The Power Point and the Excel with the data. It actually Refreshes all charts one by one.

    Sub updatelinks()
Dim sld As Slide, shp As Shape

For Each sld In ActivePresentation.Slides

   For Each shp In sld.Shapes
     On Error Resume Next
     shp.LinkFormat.Update
    Next

Next

MsgBox ("Graficos actualizados con éxito")

End Sub

So, If the Excel is on a shared location, the code wont work because it takes too much time to retrieve the data. Im still looking for a way to do this. Thanks!

Comments

[mooseman]

One change may be needed. In PPT 2016 the s.Type may need to be = 7, instead of msoEmbeddedOLEObject

[Steve Rindsberg]

msoEmbeddedOLEObject is a defined constant = 7 in every version of PowerPoint from 2016 back to PPT 97 when VBA first appeared in PPT. So long as the code's running in PowerPoint, either the constant or the value will work when testing the object type.

[Antoine Colson]

For the shared location issue, have you tried using Task Scheduler to open all shared files, then running your macro? Or is the problem that your code doesn't work when the shared files are already opened by another user?

[BMLopes]

It worked, thank! As this solution needs that both Excel and Power Point files are open, a warning could be included in a message box. Like: MsgBox ("Make sure the Excel and Power Point files are open")