使用Excel VBA更新Powerpoint中的现有嵌入式图表 [英] Update existing embedded chart in powerpoint using excel vba

问题描述

我在PowerPoint中插入了一个图表.我正在使用它作为模板.我想使用Excel工作表中的数据来编辑此图表的数据.是否有一个Excel VBA代码

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中的任何图表(都有例外，可以破坏"现有图表，但这不在范围内，此处)具有ChartData属性，该属性将返回包含图表数据的Excel工作簿.

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

使用模板"幻灯片进行工作时，应该安全地假定图表数据存在于工作表1和ListObject表中(工作表中应该只有一个这样的表).

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

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

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

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

现在您已经掌握了ListObject的句柄，您需要以某种方式获取从Excel中获取值.

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

这将需要处理Excel.Application类的打开实例(或提示用户从FileDialog中选择文件等)，并确定要在PowerPoint中放入哪些数据，以及如何对其进行排列.通常，这可以通过以下方式完成:将Excel中的值转储到变量数组中，然后将其传递给PowerPoint.

This will require handling an open instance of Excel.Application class (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.

由于所有这些细节您都已省略，请注意，我绝对不愿意接受可能永无休止的系列文章但是我该如何做……".后续问题，因为您可以避免自己的逻辑和用例需求的复杂性.

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.

以上代码旨在从PowerPoint执行.如果您需要从Excel中运行它，它将需要不同的代码(未经测试，但类似这样).

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

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

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

EDIT It is possible to edit an existing chart without Activating the ChartData.Workbook as can be demonstrated here:

在不打开图表工作簿的情况下更新PowerPoint图表或使其不可见

但是，从图表中添加/删除系列比处理已经属于图表系列的数据要复杂得多.

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

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