如何“刷新数据"通过 Power Point 中的 VBA? [英] How to "Refresh Data" via VBA in Power Point?

问题描述

到目前为止，我已经尝试了 Chart.Refresh 和 Chart.Update 以及 ChartData.UpdateLinks，但都没有奏效.我的问题与此类似，只是此代码不适用于我的 ppt如何在powerpoint中更新excel嵌入图表?

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?

如果我可以像在 Excel 中那样录制宏，步骤将是:

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

1. 选择图表

1. Select Chart

图表工具 >刷新数据

这是我设法编写的代码，但它在gChart.Application.RefreshData"失败:

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

包含整数 i 以从 i=1 到 73，但作为测试，我使用幻灯片 3.并非所有幻灯片都有图表，但大多数幻灯片都有 4 个图表(73 个中有 65 个).

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).

推荐答案

我稍微更改了代码，通过这个小小的更改，图表的刷新再次自动进行.

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

很多时候，如果你分享你的 excel ppt 组合，链接会中断，在恢复它们后，自动图表刷新不起作用.

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

使用 downdown 宏，自动刷新将再次起作用:

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

以下代码位于 Excel 工作簿中的一个宏中，该宏还包含源数据.不确定从 PowerPoint 运行它的代码是否相同.我只需打开我的 Excel 工作簿，然后让它为我更新 PowerPoint.

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.

我一直在寻找这个问题的答案，最终通过大量阅读和反复试验设法让它发挥作用.我的问题是我的 PowerPoint 中有很多图表是用 CTRL+C 和 CTRL+V 创建的，所以它们都没有链接.这就是我让它工作的方式:

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.

这篇关于如何“刷新数据"通过 Power Point 中的 VBA?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！