Update PowerPoint chart using VBA

Question

I know that this question has already been asked and answered many times, but unfortunately can't find working solution.

So, I have a very simple presentation in PowerPoint (just one slide with one chart that was created from Excel table) and need to update it with recent data via VBA, no matter from Excel or PowerPoint script should run.

First of all, I tried the most obvious script from PowerPoint:

Sub update1()
ActivePresentation.UpdateLinks
End Sub

It seems to run but made no changes. Then I began to search in web for solutions and find, for example, the following topic on StackOverflow.

For an answer by Optimistic Busy it run without errors and gives me an output in MessageBox, but it changes nothing in PowerPoint chart.

For an answer by rinusp it gives me an error

Run-time error '91': Object variable or With block variable not set

on the line

For each sld in myPresentation.Slides

I tried all these macros in PowerPoint.

I also tried answers from other questions on StackOverflow, but unfortunately nothing works for me. I will be happy if anyone helps me to find any working solution -- doesn't matter from Excel or PowerPoint should VBA script be run.

Thanks in advance.

UPDATE: I'm updating my question with complete examples of code I have tried to run. This examples were provided by users Optimistic Busy and rinusp on the mentioned above StackOverflow topic.

This code when run from PowerPoint gives me an error "Run-time error '91': Object variable or With block variable not set"

Sub update2()

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

End Sub

and this code runs without errors and gives an output in message box, but doesn't update chart

Sub update3()
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 ("Update chart")

End Sub

Hi there, can you post the full code you are trying to get working. The error appears to be related to not having a reference to the object you are interacting with. — Ryan Wildry
@RyanWildry, I updated my question with complete examples of code. — Hasek

Dy.Lee Dy.Lee · Accepted Answer · 2017-07-26T01:54:36

If you run macro in powerpoint and your chart is linked, this code will work.

Sub update2()

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

Set myPresentation = ActivePresentation

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
            Set Wb = myChart.ChartData.Workbook
            Set App = Wb.Application
            Wb.Close (0)
        End If
    Next
Next
App.Quit
End Sub

Update PowerPoint chart using VBA

1 Answers