从PowerPoint幻灯片中检索Excel图表数据(以编程方式) [英] Retrieve excel chart data from powerpoint slide (programmatically)

问题描述

背景

background

在PowerPoint中工作时，我总是使用直接位于图表后面并链接到源工作簿的Excel电子表格.此方法可确保:

When working in PowerPoint I always use an Excel spreadsheet that sits directly behind the chart, and links to a source workbook. This method ensures that:

1. 文件后面的数据源易于识别(链接到网络).
2. 如果需要，可以直接编辑PowerPoint文件.
3. 可以通过将基础电子表格重新链接到源工作簿来针对新场景更新图表.

问题

issue

最近，我遇到了一个PowerPoint文件，该文件需要使用数据来创建新图表.即使以某种方式使用我上面描述的方法创建了图表，也无法访问基础数据.我不希望我的小组手动获取数据，所以我寻找一种在情况再次发生时可以再次使用的方法.

Recently I came across a PowerPoint file that I needed to use the data to create a new chart. Somehow even though the chart had been created using the method I described above, the underlying data could not be accessed. I didn't want my group to have retrieve the data manually so I looked for a method that I could use again if the situation rec-occurred.

第一种方法

first approach

我最终遵循了

I ended up following the approach outlined at magicbeanlab which involved:

• 将PPT文件剪切为一张幻灯片(带有我想要的图表).
• 将PPT文件重命名为 zip .
• 导航到/ppt/charts/目录以 xml 格式获取图表.
• 打开 xml 文件可以访问数据，但这是其他众多信息之一.

• cutting the PPT file to a single slide (with the chart I wanted).
• renaming the PPT file as a zip.
• navigating to the /ppt/charts/ directory to get the chart in xml format.
• opening the xml file provided access to the data, but this was among a seas of other information.

问题

question

有什么更好的方法(自动进行XML检索)或使用VBA获取要在其他地方使用的图表数据?

What is a better method (automating the XML retrieval) or using VBA to obtain the chart data to use elsewhere?

推荐答案

安迪·波普提供了此答案，该将数据从PowerPoint图表中提取到剪贴板.

Andy Pope provided this answer which extracts the data from a PowerPoint chart to the clipboard.

这时可以将其直接放回到Excel中.

At this point it can be dropped directly backed into Excel.

好工作的安迪.

Sub RipChartValues()

Dim cht As PowerPoint.Chart
Dim seriesIndex As Long
Dim labels As Variant
Dim values As Variant
Dim name As String
Dim buffer As String
Dim objData As Object

Set cht = ActiveWindow.Selection.ShapeRange.Parent.Shapes(ActiveWindow.Selection.ShapeRange.name).Chart

With cht
    For seriesIndex = 1 To .SeriesCollection.Count
    name = .SeriesCollection(seriesIndex).name
    labels = .SeriesCollection(seriesIndex).XValues
    values = .SeriesCollection(seriesIndex).values

    If seriesIndex = 1 Then buffer = vbTab & Join(labels, vbTab) & vbCrLf
    buffer = buffer & (name & vbTab & Join(values, vbTab) & vbCrLf)
    Next

End With

On Error Resume Next
' Rory's late bind example
' this is a late bound MSForms.DataObject
Set objData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

' copy current cell formula to clipboard
With objData
    .SetText buffer
    .PutInClipboard
    MsgBox "Data extracted to clipboard!", vbOKOnly, "Success"
End With

End Sub

这篇关于从PowerPoint幻灯片中检索Excel图表数据(以编程方式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！