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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Bengt" <bobone a pointer.se> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Bengt" <bobone a pointer.se> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>>
>>
>
>

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, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Bob" <bobone a pointer.se> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>> "Bengt" <bobone a pointer.se> wrote in message
>> news:%(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>>
>>
>
>

> 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, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>> 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, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Jon Peltier" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>>> 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, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> Peltier Technical Services, Inc. - http://PeltierTech.com
>> _______
>>
>>
>
>

"Bob" <bobone a pointer.se> wrote in message
news:(E-Mail Removed)...
> 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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______