vba 使用excel vba更新powerpoint中现有的嵌入式图表

Update existing embedded chart in powerpoint using excel vba

提问by Manoj Kumar

I have an inserted chart in powerpoint. I am using this as a template. I would want to edit the data of this chart with data from an excel sheet. Is there an excel vba code for this

我在powerpoint中有一个插入的图表。我正在使用它作为模板。我想用 Excel 工作表中的数据编辑此图表的数据。是否有用于此的 excel vba 代码

回答by David Zemens

Any Chart in PowerPoint (there are exceptions, and it is possible to "break" existing charts, but that's not in scope, here) has a ChartDataproperty, which returns an Excel Workbook that contains the data for the chart.

PowerPoint 中的任何图表（也有例外，可以“破坏”现有图表，但这不在范围内，此处）有一个ChartData属性，它返回一个包含图表数据的 Excel 工作簿。

When working from a "template" slide, it should be safe to assume that the chart data exists on Sheet 1, and in a ListObjecttable (there should be only one such table in the sheet).

在使用“模板”幻灯片工作时，可以安全地假设图表数据存在于第 1 页和ListObject表格中（表格中应该只有一个这样的表格）。

In PowerPoint VBA, requiring reference to Excel object library, this shows you how to get a handle on the ListObjectwhich contains the chart's data:

在 PowerPoint VBA 中，需要引用 Excel 对象库，这向您展示了如何获取ListObject包含图表数据的句柄：

Sub ShowChartData()
Dim sld As Slide
Dim shp As Shape
Dim cht As Chart
Dim chtData As ChartData
Dim cTable As Excel.ListObject

'Assume we have only one slide, at slide 1:
Set sld = ActivePresentation.Slides(1)
'Assume the Chart is the second shape, modify if needed
Set shp = sld.Shapes(2)    
'Handle the chart
Set cht = shp.Chart
'Handle the CharttData
Set chtData = cht.ChartData

'Open & minimize the ChartData, you don't need to see it, but it must be OPEN to edit it
chtData.Activate
chtData.Workbook.Application.WindowState = -4140

With chtData
    Set cTable = chtData.Workbook.Worksheets(1).ListObjects(1)

    ' Here, you can update the ListObject in the same ways you
    ' would do so in Excel, natively.

End With

'Remember to close the workbook
chtData.Workbook.Close

End Sub

Now that you have a handle on the ListObject, you need to somehow getthe values from Excel.

现在您已经掌握了ListObject，您需要以某种方式从 Excel获取值。

This will require handling an open instance of Excel.Applicationclass (or prompting the user to select a file from a FileDialog, etc.) and identifying which data to put in the PowerPoint, and how to arrange it. Usually this can be done by dumping the values from Excel in to a variant array, and passing that to PowerPoint.

这将需要处理一个打开的Excel.Application类实例（或提示用户从 FileDialog 等中选择一个文件）并确定将哪些数据放入 PowerPoint，以及如何安排它。通常这可以通过将 Excel 中的值转储到变体数组中，然后将其传递给 PowerPoint 来完成。

Since those are all details you've omitted, please note that I am absolutely not willing to entertain what is likely to be a never-ending series of "but how do I do such-and-such...?" follow-up questions as you suss out the complexities of your own logic and use-case requirements.

由于这些都是你省略的细节，请注意，我绝对不愿意接受可能是永无止境的系列“但是我该怎么做这样那样的......？” 在您确定自己的逻辑和用例需求的复杂性时提出后续问题。

The above code is designed to execute from PowerPoint. If you need to run it fromExcel, it will require different code (untested, but something like this).

上述代码旨在从 PowerPoint 执行。如果您需要从Excel运行它，它将需要不同的代码（未经测试，但类似这样）。

Sub ShowPPTChartData()
' to be run from Excel VBA
'Requires reference to PowerPoint library
Dim ppt as PowerPoint.Application
Dim pres as PowerPoint.Presentation
Dim sld As PowerPoint.Slide
Dim shp As PowerPoint.Shape
Dim cht As PowerPoint.Chart
Dim chtData As PowerPoint.ChartData
Dim cTable As Excel.ListObject

Set ppt = GetObject(,"PowerPoint.Application")
'Assume we have only one open Presentation file:
Set pres = ppt.Presentations(1)
'Assume we have only one slide, at slide 1:
Set sld = pres.Slides(1)
'Assume the Chart is the second shape, modify if needed
Set shp = sld.Shapes(2)    
'Handle the chart
Set cht = shp.Chart
'Handle the CharttData
Set chtData = cht.ChartData

'Open & minimize the ChartData, you don't need to see it, but it must be OPEN to edit it
chtData.Activate
chtData.Workbook.Application.WindowState = -4140

With chtData
    Set cTable = chtData.Workbook.Worksheets(1).ListObjects(1)

    ' Here, you can update the ListObject in the same ways you
    ' would do so in Excel, natively.

End With

'Remember to close the workbook
chtData.Workbook.Close

End Sub

EDITIt is possible to edit an existing chart without Activatingthe ChartData.Workbookas can be demonstrated here:

编辑可以在不激活的情况下编辑现有图表ChartData.Workbook，如下所示：

Update PowerPoint chart without opening chart workbook or making it invisible

无需打开图表工作簿或使其不可见即可更新 PowerPoint 图表

Adding/removing series from the charts is trickier than manipulating data that's already part of the chart series, however.

然而，从图表中添加/删除系列比操作已经是图表系列一部分的数据更棘手。

回答by Manoj Kumar

Set Current_Chart_Shape = Active_Slide.Shapes("Monthly Chart")
Set Current_Chart = Current_Chart_Shape.Chart
Set Current_Chart_Data = Current_Chart.ChartData

Current_Chart_Data.Activate
Set PPTChartSheet = Current_Chart.ChartData.Workbook.Sheets(1)
Current_Chart_Data.Workbook.Application.WindowState = -4140

With PPTChartSheet
.ListObjects("Table1").Resize PPTChartSheet.Range("A1:C8")
For l = 0 To 6
.Range("B2").Offset(l, 0).Value = Array_Values(l)
Next l
End With

Current_Chart_Data.Workbook.Close

回答by durbnpoisn

You don't need to do anything special.

你不需要做任何特别的事情。

Once a chart is embedded and linked to an Excel sheet, PowerPoint couldn't care less when or how it's updated, even when the PowerPoint file is closed.
Basically, whatever you do to that sheet, VB or manually, will be reflected in the PowerPoint next time you open it.

一旦图表被嵌入并链接到 Excel 工作表，PowerPoint 就不会关心它的更新时间或方式，即使 PowerPoint 文件已关闭。
基本上，无论您对该工作表做什么，无论是 VB 还是手动，都会在您下次打开时反映在 PowerPoint 中。