PPT 2007 - Accessing msoChart data table

[Cosmo]

I remember when 2007 was first released, that there were issues with accessing chart data with VBA (issues being that it wasn't do-able). I believe these issues were fixed in later updates. I need to update some code to access these charts, but I can't determine how exactly to access the cell data.

I can access the chart title using activePresentation.Slides(1).Shapes(1).Chart.ChartTitle.Text
(or .ChartTitle.Caption - not sure what/if there is any difference)

Can anyone tell me how to access the data in the chart?

[Cosmo]

Came across this (http://www.thecodecage.com/forumz/microsoft-powerpoint-formatting/104552-how-manipulate-chart-objects-ppt2007-sp2.html) from the ever-knowledgeable Steve Rindsberg:

Dim oSh as Shape
Dim oSl as Slide

For each osl in activepresentation.slides
for each osh in osl.shapes
` if osh.type = msoChart then
With osh.chart.chartdata
' chartdata gives you a reference to the data
.Activate

' Minimize it; ugly but it's straight from
' MSMouth ... presumably it's the best they can do. Sigh.
.Workbook.Application.WindowState = -4140

' change the first data point
.Workbook.Worksheets("Sheet1").Cells(2,2) = 123

' and quit the workbook
.Workbook.Application.Quit

end with
next
next
next


Is this really the only way?!? No wonder I was having problems, I had noticed 'Activate' and 'Workbook' in the chart object, but never thought I'd need to use them both to access the Workbook.

It won't be acceptable to my client to have the excel window flashing on/off when setting data using my program. I'm really hoping there's a better way out there somewhere.

[John Wilson]

Hi Mark

I know you know this:
Steve can't fix it - it's very broken!
John AND Steve can't fix - It's totally Bu******ered.

You can only work on the workbook if you activate the chartdata and then you're gonna see it. If you make the datasheet small and in a corner then it will look better but still unacceptable to me. If you do manage to solve it email me!!!!

[Cosmo]

Hi Mark

I know you know this:
Steve can't fix it - it's very broken!
John AND Steve can't fix - It's totally Bu******ered.

You can only work on the workbook if you activate the chartdata and then you're gonna see it. If you make the datasheet small and in a corner then it will look better but still unacceptable to me.
That's what I figured; it's been a few years since I had to address this, and I can't remember just how far I had gotten looking into this back then. I must have blocked those ugly attempts out of my memory.


If you do manage to solve it email me!!!!
I wouldn't hold my breath; If John and Steve can't find a better solution, it's highly unlikely that I would. For now, my only solution is what I have been doing since 2007 came out - having my program save it's documents back to 2003 format.