Copy Chart Data into New Excel Wkbk

[swayp11]

I am trying to loop through a presentation that has charts and I would like to grab the data from each chart and paste into Excel.

I think I am doing this right, first I loop through slides, then shapes:



Dim ppSlide As PowerPoint.Slide
Dim ppShape As PowerPoint.Shape
Dim Cht1 As Chart
Dim myChartdata As ChartData
Dim myWkbk As Excel.Workbook
Dim myWksht As Excel.Worksheet


For Each ppSlide In ActivePresentation.Slides
For Each ppShape In ppSlide.Shapes
If ppShape.Type = msoChart Then
MsgBox ("x")
End If

Next
Next

I never get inside the if statement because I assume that the chart is not an object but rather just a chart (for example, Chart 3). How can I get to the underlying Excel sheet of this chart so I can copy the data the chart is referencing?

Then, I am going to try and open a new excel window and paste it all in the first sheet. I'm trying something like this:



Dim newExcel As Excel.Application
Set myWkbk = newExcel.Workbooks.Add

mywkbk.worksheets(1).range("A1").paste
...



Thanks for any tips or points in the right direction!



PowerPoint/Excel 2007

[John Wilson]

First vba support for charts in PPT 2007 is very buggy!

Also 2007 has a nasty habit of inserting charts into placeholders. The code will not detect them as charts but will see them as Placeholders (Type 14)

See if this works better

If ppShape.HasChart Then

[swayp11]

Thank you for the reply!

When looping through shapes, I can use the hasChart (which I assume returns a Boolean) if statement and then proceed to access the workbook/worksheet of the chart data if it returns true.

Will the hasChart work if it is a chart object OR a placeholder object? Or do I need to run a number of if statements to figure out what kind of object it is and run separate code if it is a chart, an OLE object, a placeholder, etc.?