In my last post, 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
_______
"Jon Peltier" wrote in message
...
I can't comment on the text box items, though I know I have already built
workarounds for some of the quirkiness of textboxes in 2007 and
particularly textboxes in charts in 2007.
As far as the second item, the charts which are made in PowerPoint (and
presumably Word) using the new Office-wide Excel charting capability are
not exposed to VBA. To me this removes any advantage of having an
Office-wide charting capability.
If you keep using the approaches that work in Office 2003 & earlier, that
is, you create the chart in Excel, then insert it into PowerPoint/Word as
an Excel chart object, the result is an OLE object which VBA can
manipulate.
- 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
...
Ahhhh,
2007 is where it becomes interesting!!!
I have found one bug and one nuisance:
- nuisance: the resize function "leftaligns" everything despite alignment
settings, i.e. the whole box is only changed on the right hand side!
- bug: can't seem to programatically insert boxes further to the right
than a certain point being approximately the plot area border when a
legend is to the right, independently of the actual plot area border
(even if moved to chartarea border) and if there is a legend or not. Same
problem from the bottom.
I ask a question again that must have been asked 100 times:
- is it 100% true that it is impossible to reach the embedded Charts
(excel) 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 ca'nt understand whay this door has
been closed?
Best regards,
Bob
"Andy Pope" wrote in message
...
Hi,
I will post the code Brian is referring to on my website.
Just need to find some time to tidy the code and check it works in
xl2007.
The code is design to run from within excel. Any charts created could
then be imported/linked to Powerpoint.
Cheers
Andy
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Bengt" <bobone a pointer.se wrote in message
...
Hi,
This piece of code would be very valuable to me....
Could you please post the essentials of this?
One of the major problems I am having with 2007 is the Autoresize /
Resize shape to fit text.
It seems to neglect alignments compared to 2003.
Bob
"Brian Reilly, MVP" wrote in message
...
Brian,
The data label boxes are impossible to format but Andy Pope, an Excel
MVP, wrote a piece of VBA code for me recently that took the data
label values and turned them into real text boxes and realigned them.
It is very sweet code that handles something that can't be easily done
in the native interface.
If you are comfortable with some VBA code in your presentations (or in
your Excel workbook), you might want to chase Andy down in the Excel
charting newsgroup. Andy also shows up here from time to time.
Brian Reilly, MVP
On Mon, 28 Jan 2008 17:06:00 -0800, Brian Lynn
wrote:
Just so you know, I can see the bounding box for each individual data
label,
and when I click them I can see the little round handles for resizing,
but
when I try to resize it does nothing...
"Brian Lynn" wrote:
I can't seem to be able to resize chart text boxes on PPT2007. I can
resize
the text font itself, but I can't resize the box the text is in,
which means
long company titles are wrapping funny and I have orphan letters...
How do I unlock this so I can resize the data labels manually?