Extracting data from embeded spreadsheets in Powerpoint Charts

[LucasLondon]

Hi,

Is it possible using VBA to extract the underlying data from a chart created in powerpoint, i.e from the underlying datasheet?

I have a series of charts in powerpoint in multiple slides. When u click on the chart, the underlying datasheet holding the source data can be viewed.

I would like to copy the range "A1:H20" from all charts (the underlying sheet) in powerpoint on the slide 10 to 23 and paste it into a new normal excel workbook.

Would this be easy to do?

Thanks

Lucas

[LucasLondon]

Hi,

In relation to the above post, I've manabed to get hold of some code that extracts the underlying data from charts/datasheet in powerpoint.

It suppose to data from in each chart and slide in powerpoint but I'm struggling with the bit to get the data into excel. The excel bit does not work at all, I hope someone can help.

Thanks,

Lucas

Sub GetChartData2() 'copies data from sheet
Dim s As Shape 'gr As Graph.Chart
Dim gr As Object
Dim sl As Slide
'Copies data from datasheet in powerpoint
For Each sl In ActivePresentation.Slides
For Each s In sl.Shapes
If s.Type = msoEmbeddedOLEObject Then
'we have found an OLE object
'check if it's a graph
If s.OLEFormat.ProgID = "MSGraph.Chart.8" Then
'this might vary depending on what version you're using
'now get a handle on the graph object itself
Set gr = s.OLEFormat.Object
gr.Application.DataSheet.Cells.Copy

'Paste into excel - this section not working

Workbooks("test.xls").Sheets("sheet1").Activate
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If
End If
Next s
Next sl

End Sub