使用 VBA 更新 PowerPoint 图表 [英] Update PowerPoint chart using VBA

问题描述

我知道这个问题已经被问过很多次了，但很遗憾找不到可行的解决方案.

因此，我在 PowerPoint 中有一个非常简单的演示文稿(只有一张幻灯片，其中一个图表是从 Excel 表格创建的)，并且需要通过 VBA 使用最近的数据更新它，无论是从 Excel 还是 PowerPoint 脚本都应该运行.

首先，我尝试了 PowerPoint 中最明显的脚本:

Sub update1()ActivePresentation.UpdateLinks结束子

它似乎在运行，但没有进行任何更改.然后我开始在网上搜索解决方案并找到例如以下 StackOverflow 上的话题.

对于 Optimistic Busy 的回答，它运行没有错误，并在 MessageBox 中给我一个输出，但它在 PowerPoint 图表中没有任何改变.

对于 rinusp 的回答，它给了我一个错误

<块引用>

运行时错误91":对象变量或未设置块变量

上线

对于 myPresentation.Slides 中的每个 sld

我在 PowerPoint 中尝试了所有这些宏.

我也在 StackOverflow 上尝试了其他问题的答案，但不幸的是没有任何东西对我有用.如果有人帮助我找到任何可行的解决方案，我会很高兴 - 如果运行 VBA 脚本，Excel 或 PowerPoint 无关紧要.

提前致谢.

更新:我正在用我尝试运行的完整代码示例更新我的问题.此示例由用户 Optimistic Busy 和 rinusp 在上述 StackOverflow 主题上提供.

从 PowerPoint 运行此代码时出现错误运行时错误 '91':对象变量或块变量未设置"

Sub update2()将 myPresentation 调暗为 PowerPoint.Presentation将幻灯片变暗为 PowerPoint.Slide将 shp 调暗为 PowerPoint.Shape将 myChart 变暗为 PowerPoint.Chart对于 myPresentation.Slides 中的每个 sld对于 sld.Shapes 中的每个 shp如果 shp.HasChart 那么设置 myChart = shp.ChartmyChart.ChartData.ActivatemyChart.Refresh万一下一个下一个结束子

并且此代码运行没有错误并在消息框中给出输出，但不更新图表

Sub update3()Dim sld As Slide, shp As Shape对于 ActivePresentation.Slides 中的每个 sld对于 sld.Shapes 中的每个 shp出错时继续下一步shp.LinkFormat.Update下一个下一个MsgBox ("更新图表")结束子

解决方案

如果您在 powerpoint 中运行宏并且您的图表已链接，则此代码将起作用.

Sub update2()将 myPresentation 调暗为 PowerPoint.Presentation将幻灯片变暗为 PowerPoint.Slide将 shp 调暗为 PowerPoint.Shape将 myChart 变暗为 PowerPoint.ChartDim Wb 作为对象Dim 应用程序作为对象设置 myPresentation = ActivePresentation对于 myPresentation.Slides 中的每个 sld对于 sld.Shapes 中的每个 shp如果 shp.HasChart 那么设置 myChart = shp.ChartmyChart.ChartData.ActivatemyChart.Refresh设置 Wb = myChart.ChartData.Workbook设置 App = Wb.ApplicationWb.关闭 (0)万一下一个下一个应用程序退出结束子

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

解决方案

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

这篇关于使用 VBA 更新 PowerPoint 图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！