Retrieve excel chart data from powerpoint slide (programmatically)

Question

background

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:

The data source behind the file is readily identifiable (link to network).
The PowerPoint file can be edited directly if needed.
The chart can be updated for a new scenario by re-linking the underlying spreadsheet to the source workbook.

issue

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:

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

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

>> "Somehow even though the chart had been created using the method I described above, the underlying data could not be accessed." Some years ago, Microsoft introduced a nasty habit into PowerPoint; they allow you to copy data/chart from an as-yet-unsaved Excel file and paste-link it into PPT. This creates a "link" to a file that doesn't exist. Someone might have inadvertently done this; if so, it would explain the behavior you're seeing. — Steve Rindsberg
– Steve Rindsberg, Commented Sep 25, 2023 at 15:33

brettdj · Accepted Answer · 2016-04-16 07:38:05Z

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

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

Nice work Andy.

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

Collectives™ on Stack Overflow

Retrieve excel chart data from powerpoint slide (programmatically)

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related