Manipulate Excel Charts in Power Point programatically

[Guest]

Hello everybody,

I'm trying to manipulate Excel Charts embedded in Power Point using VBA,
I've succedded manipulating native PPT charts but I can't figure out how to
do the same with an embedded excel chart.

I managed to add an Excel object doing this:
Dim objGrafica as Object
Set objGrafica = ActivePresentation.Slides(1).Shapes.AddOLEObject(Left:=100,
Top:=100, Width:=400#, Height:=250#, ClassName:="Excel.Chart.8",
Link:=msoFalse).OLEFormat.Object

But I don't know how to manipulate it (change its type, change the data
source, change the colours...). I tried to use this instruction:

objGrafica.ChartType = xlXYScatter

but it doesn't work.

By the way, is there a place where I could find GOOD information about VBA?
I only find pieces in the help files. I would like something like PHP or Java
documentation where you can find almost ALL the objects in one place.

 

[Steve Rindsberg]

Search Google for terms like "automating Excel" ... that should lead to all
kinds of interesting reading. But also see the comment below:

I'm trying to manipulate Excel Charts embedded in Power Point using VBA,
I've succedded manipulating native PPT charts but I can't figure out how to
do the same with an embedded excel chart.

I managed to add an Excel object doing this:
Dim objGrafica as Object
Set objGrafica = ActivePresentation.Slides(1).Shapes.AddOLEObject(Left:=100,
Top:=100, Width:=400#, Height:=250#, ClassName:="Excel.Chart.8",
Link:=msoFalse).OLEFormat.Object

But I don't know how to manipulate it (change its type, change the data
source, change the colours...). I tried to use this instruction:

objGrafica.ChartType = xlXYScatter

objGrafica is a reference to a shape that holds the graph object.
It might be better to do:

Dim oSh as Shape
Dim objGraphfica as Object

' Create the object, get a reference to the shape:
Set oSh = ActivePresentation.Slides(1).Shapes.AddOLEObject(Left:=100,
Top:=100, Width:=400#, Height:=250#, ClassName:="Excel.Chart.8",
Link:=msoFalse).OLEFormat.Object

' and NOW get a reference to the chart in the shape:
Set objGrafica = oSh.OLEFormat.Object

' so now you can change the chart:
' But you can't use xlScatter since that's not defined in PowerPoint
' You have to use the numeric value instead
' In Excel's VBA editor, press Ctrl+G to open the Immediate window
' In it, type ? xlScatter to get the value of xlScatter
' Use that below:
objGraphica.ChartType = 5 ' 5 is a pie, not a scatter

 

[Guest]

Thanks Steve, unfortunately the code generated a Type Mismatch in the
instruction "Set oSh = ..." I guess because of the oSh is declared as a Shape.
Anyway I solved doing this:

Dim objExcel As Workbook
Dim objGrafica As Excel.Chart
Dim objHoja As Excel.Sheets

Set objExcel =
ActivePresentation.Slides(UltimaSlide).Shapes.AddOLEObject(Left:=posLeftG,
Top:=posTopG, Width:=410#, Height:=250#, ClassName:="Excel.Chart.8",
Link:=msoFalse).OLEFormat.Object

Set objGrafica = objExcel.Charts(1)
Set objHoja = objExcel.Sheets(2)
objGrafica.ChartType = xlXYScatter

Yeah I've searched a lot in Google and I only find pieces as I told you.
There are many things about VBA in Excel but they doesn't work well here, for
reasons that only MS knows since the KB seems pretty lame in this aspect.

But that's not the end of the story, now I only have a partial solution, I
tried to delete the series:

objGrafica.SeriesCollection(1).Delete

and worked (yay!), then I tried to do

objHoja.Cells.Select
objHoja.Selection.ClearContents

and it didn't work I thought it was a problem with the sheet, but
strangely I can manipulate individual cells, for example:

objHoja.Cells(4, 4).Value = "XYZ"

So, there are things that work and things tha doesn't work? Someone knows?
I'm using PowerPoint 2002. I wonder why MS don't give enough documentation.
Making information obscure is absurd.

 

[Steve Rindsberg]

Thanks Steve, unfortunately the code generated a Type Mismatch in the
instruction "Set oSh = ..." I guess because of the oSh is declared as a Shape.

O nuts. My bad. SOrry about that.

As far as getting help on controlling Excel, another very useful trick is to start
in Excel, record a macro while you do what you need, then modify the references to
get it working in PPT.

 

[Guest]

As far as getting help on controlling Excel, another very useful trick is to start
in Excel, record a macro while you do what you need, then modify the references to
get it working in PPT.

Yeah I already tried that with no success, I think I'm going to move to
other things and solve my problems with another tool, because working with
embedded charts is pretty annoying, well it is Microsoft after all