Accessing PowerPoint Chart with VBA

[Chris Macro]

I am wondering how to access a PowerPoint chart with VBA. I would like to change the y-axis parameters for my chart but can't seem to figure out how to get down to that level. I found the following example code in another forum but receive an error on the With Statement line of code. Any thoughts?


Sub Test()

Dim oSl As PowerPoint.Slide
Dim oSh As PowerPoint.Shape


Set oSl = ActivePresentation.Slides(1)
Set oSh = oSl.Shapes(1)
With oSh.OLEFormat.Object.WorkSheets(1)
.Range("A1").Value = .Range("A1").Value + 1
.Range("A2").Value = .Range("A2").Value - 1
End With


Set oSl = Nothing
Set oSh = Nothing


End Sub

[John Wilson]

That looks like code for the old style MSGraph (from 2003)

Chart vba is pretty broken in 2007 but in theory charts are treated much the same as in Excel.


Sub charter()
Dim ocht As Chart
Set ocht = ActivePresentation.Slides(1).Shapes(3).Chart
With ocht.ChartData.Workbook.worksheets(1)
.Range("B2") = 50
.Range("C2") = 12
End With
End Sub



Be aware though this will error if the data sheet is not still open. Leave it minimised and see if that works (it does in 2010)

[Chris Macro]

I am getting a Method not Found Error in Excel 2007. I even added the Excel Object Library and still no luck...may it is really, really broken in 2007!

Set ocht = ActivePresentation.Slides(1).Shapes(3).Chart

[John Wilson]

I just fired up a 2007 PC. Yep it is broken in 2007 - there's no chart object as there is in 2010 / 13

[Chris Macro]

Ok, well I will have to wait until I get home then to see if I can figure it out on Excel 2013. Will keep you posted on my progress.

[John Wilson]

Chris

I found out the 2007 box had not been updated. Installed SP 3 and it is easier (not good but easier)

There is a shp.Chart.ChartData object but you must activate before it can be changed (have it minimised).