Changing Excel charts pasted into PPT 2007

[Bengt]

Hi,

I ask a question that must have been asked 100 times about Office 2007:
- is it 100% true that it is impossible to reach pasted Excel charts in PPT
programatically. I.e. what whas done before by addressing the Activechart of
the OLEObject.

This is a nightmare for all code that I have developed that cleanses charts
pasted in PPT from Excel. I can't understand whay this door has been closed?
Is this forever?

Best regards,

Bob

 

[Jon Peltier]

In the post in the other group, I said that Office-wide (Excel) charts built
in PowerPoint were not accessible to VBA, but that charts made in Excel and
then inserted into PowerPoint were accessible through the familiar OLE
approach. This was only partially true.

If an Excel 2007 chart is copied in Excel and Pasted into PowerPoint, it
behaves as one of the new Office-wide Excel charts. It is inaccessible to
VBA. You can access the shape itself, but there is no OLE Object in it which
you can hook into. They've even added a property, HasChart, to tell you that
the shape contains a chart, but there is no Chart object that you can hook
into. I don't know whether to blame it on a design error or on a lack of
time to implement, but it is a major shortcoming in Office 2007.

There is hope, however. You can insert the chart from a file. Make sure the
workbook is open but saved such that the chart is the active chart sheet,
then use code similar to this to insert it into PowerPoint (this is code
running in Excel VBA):

' insert from excel file
Set ppShape = ppSlide.Shapes.AddOLEObject _
(Left:=90#, Top:=240#, Width:=360#, Height:=240#, _
Filename:=ActiveWorkbook.FullName, Link:=msoFalse)
With ppShape
.Name = "xlInsertedSheet"
.Width = ActiveChart.ChartArea.Width
.Height = ActiveChart.ChartArea.Height
.Left = (ppPres.PageSetup.SlideWidth - .Width) / 2
.Top = (ppPres.PageSetup.SlideHeight - .Height) / 2
End With

Now you can manipulate the chart in the PowerPoint shape named
xlInsertedSheet, using OLE to hook into the chart.

- Jon

 

[Bob]

Hi Jon, Brian, Steve and all,

Jon's and Brian's approach of inserting OLE objects from files through code
is some relief.
I haven't yet tested, but does this implicitely require a link to work?
Links would be cumbersome to use in this case.

Also as Steve pointed out, already created presentations with pasted charts
are problematic. If one could move them to a file and then insert them
programatically, life would be better....
In the middle of this mail I started fooling around with opening charts and
copying them between files. Quite messy, but there seems to be some
interesting oppportunities coming up.

It's getting late over here, so I'll try tomorrow.

Bob

 

[Jon Peltier]

No links are created when the workbook is inserted as an object. Another
option is to insert it as a link, but it is not necessary.

I haven't tried this through automation, and I even question whether it's
possible, but manually you can edit the data to view the pasted chart in
Excel, do a save-as in Excel, clean up the saved workbook, then insert it as
an object into the slide.

- Jon

 

[Jon Peltier]

I haven't tried this through automation, and I even question whether it's

possible, but manually you can edit the data to view the pasted chart in
Excel, do a save-as in Excel, clean up the saved workbook, then insert it
as an object into the slide.

Okay, so I tried by automation.

Sub Noodling()
Dim shp As Shape
Dim iShpType As MsoShapeType

Set shp = ActiveWindow.Selection.ShapeRange(1)
iShpType = shp.Type

If iShpType = msoChart Then MsgBox "The shape is a chart"
If shp.HasChart Then MsgBox "The shape has a chart"
' there is no indication what can be done with shp
' object browser, autosense, online help all have nothing

End Sub

I can find out that the shape is a chart or has a chart, but I can divine no
way to access the contained chart via VBA. It seems like a dead end.

- Jon

 

[Jon Peltier]

The macro recorder has been deprecated from PowerPoint 2007, so I switched
to Word, pasted in a chart, turned on the recorder, edited the chart's
source data (which is linked to the file the chart was pasted from, despite
having not selected Linked while using paste special), and read the recorded
code. I reproduce it here in its entirety:

Sub Macro1()
'
' Macro1 Macro
'
'
End Sub

Why am I not surprised?

- Jon

 

[Bob]

I've spent some time fooling around with this...not getting anywhere!

VERY frustrating indeed!

At some point with 2003 I was forced to write some code that adressed the
command bars in order top solve a problem (resizing charts) by opening and
updating them:
Set myUpdate = myApp.CommandBars("File").Controls("&Update")
myUpdate.Execute

Two-three questions:
1)
a)would it be possible to do something similar with the
Ribbon/Group/Commandbars ? I.e. address them and then "press" on them
programatically?
b) Evne better would be to address the contextual menu shown when
rightclicking on a chart.

2) I started investigating what was found under Application.Commandbars.
Seems to be the old menu items?!!!


Bob

 

[Jon Peltier]

I've spent some time fooling around with this...not getting anywhere!

VERY frustrating indeed!

At some point with 2003 I was forced to write some code that adressed the
command bars in order top solve a problem (resizing charts) by opening and
updating them:
Set myUpdate = myApp.CommandBars("File").Controls("&Update")
myUpdate.Execute

Two-three questions:
1)
a)would it be possible to do something similar with the
Ribbon/Group/Commandbars ? I.e. address them and then "press" on them
programatically?

I was looking into this the other day. I didn't find anything, but I might
not yet know where to look.

b) Evne better would be to address the contextual menu shown when
rightclicking on a chart.

You can change these using the same code that worked in 2003. I'll bet you
can use .Execute with one. I just ran this from the VB Editor's Immediate
Window:

application.commandbars("worksheet menu
bar").Controls("File").controls("Save As...").execute

and the Save As dialog came up.

2) I started investigating what was found under Application.Commandbars.
Seems to be the old menu items?!!!

They are still there, sort of. That's why the above command must have
worked.

- Jon