Trouble with OLEFormat in powerpoint using VBA

[DevM]

Hello,
I have been tasked with updating (always fun) an old Access to Powerpoint Visual Basic program from Office 2003 to 2007. I am running into a problem where I get an error when trying to access the OLEFormat of slides the program has generated. The error I get is, "OLEFormat (unknown member): Invalid request. This property only applies to OLE objects."
The code is paraphrased (I do not include it all as the subroutine is 34 pages long) as follows:

Function CreateGraph(CGFF_Tablename as string, objPPPres as PowerPoint.Presentation..........etc.)

Dim oDatasheet as Object
Dim lRowCnt as Long
Dim currDB as DAO.Database
Dim Rec_Set as DAO.Recordset
Dim CGFF_field as DAO.Field
Dim pptGraph as Graph.Chart
Dim intCnt as Integer
Dim intRecCnt as Integer

' Does a bunch of stuff to set up the chart tables

' Loop through all of th eslides for this report type.
For intSlideCnt = 1 to intNumSlides
intFieldCnt = 1
int ShpCnt = 0
' If need to start a new slide
If (intRecCnt = 0) and (lFowCnt<intNumRows) Then
' Add a new slide
Set objPPSlide = AddNewSlide(objPPPres, ppLayoutTitleOnly, strSlideTitle)
pptGraph.Application.Update
DoEvents
End If

'add the graph
Set pptGraph=objPPSlide.Shapes.AddOLEObject(Left:=lLeft, Top:=lTop, Width:=lWidth, Height:=lHeight-50, ClassName:="MSGraph.Chart", Link:=0).OLEFormat.Object

' More processing goes on here repeating the creation of slides several times. Now to get the scale on the slides all the same.

' If there were more than one slide for this report
if intNumSlides > 1 then
if pptGraph.Axes(xlValue).MaximumScale>intMaxScale then
intMaxScale=pptGraph.Axes(xlValue).MaximumScale
End If

Set pptGraph=Nothing
' Loop through all of the slides for this report and update the maximum scale
For intCnt=intStartSlideNum to intSlideNum
********This is the line that is failing********
Set pptGraph = objPPPres.Slides(intCnt).Shapes(2).OLEFormat.Object
With pptGraph
.Axes(xlValue).MaximumScale = intMaxScale
.Axes(xlValue).MinimumScale = 0
End With
Set pptGraph = Nothing
Next intCnt
End If

' More processing

End

Blame any typo's in the above code on me. It compiles just fine. I had to transcribe it by hand.

Any insight into why this procedure, which works fine in 2003 by the way, fails now would be helpful.

Thank you,
Paul

 

[SkipVought]

Hi,

2007 no longer uses the old klunky MS Graph object.

Rather it is an Excel 2007 Chart with an embedded Excel worksheet for the source data. Much more versatile and simpler to work with!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

FYI, from Excel Help What's new in Microsoft Office Excel 2007

Shared charting
Using Excel charts in other programs In 2007 Office release, charting is shared between Excel, Word, and PowerPoint. Rather than using the charting features that are provided by Microsoft Graph, Word and PowerPoint now incorporate the powerful charting features of Excel. Because an Excel worksheet is used as the chart data sheet for Word and PowerPoint charts, shared charting provides the rich functionality of Excel, including the use of formulas, filtering, sorting, and the ability to link a chart to external data sources, such as Microsoft SQL Server and Analysis Services (OLAP), for up-to-date information in your chart. The Excel worksheet that contains the data of your chart can be stored in your Word document or PowerPoint presentation, or in a separate file to reduce the size of your documents.

Copying charts to other programs Charts can be easily copied and pasted between documents or from one program to another. When you copy a chart from Excel to Word or PowerPoint, it automatically changes to match the Word document or PowerPoint presentation, but you can also retain the Excel chart format. The Excel worksheet data can be embedded in the Word document or PowerPoint presentation, but you can also leave it in the Excel source file.

Animating charts in PowerPoint In PowerPoint, you can more easily use animation to emphasize data in an Excel-based chart. You can animate the entire chart or the legend entry and axis labels. In a column chart, you can even animate individual columns to better illustrate a specific point. Animation features are easier to find and you have a lot more control. For example, you can make changes to individual animation steps, and use more animation effects.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DevM]

Skip,
Thanks for your input. Does this mean I need to retool my entire VB Script to use the excel codes? If so, where do I start? With how the objects are created? Because the program still creates the graphs just fine. I just seem to be having trouble accessing and adjusting them.

Thank You,
Paul

 

[SkipVought]

You ought to be able to.

The error I get is, "OLEFormat (unknown member): Invalid request. This property only applies to OLE objects."

On what statement?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DevM]

The line that is causing the error is Set pptGraph = objPPPres.Slides(intCnt).Shapes(2).OLEFormat.Object. I marked it with a note in my first post.

I did read an article (

http://msdn.microsoft.com/en-us/library/bb871574(VS.80).aspx)on

creating excel graphs which says (towards the bottom) that graphs imported into powerpoint are "copied as a picture". Could this be the cause of my inability to access it once it is added to the slide?

 

[SkipVought]

Set pptGraph = objPPPres.Slides(intCnt).Shapes(2).OLEFormat.Object.

will throw an error IF the referenced object is not an OLE object.

Are you absolutely sure that the SECOND SHAPE on the slide is the correct shape?

Use your Watch Window to discover the necessary information.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DevM]

Thanks for keeping up with me. It's been a pain. Actually I used the immediate window and found that the only object on the slide is number 4. I tried changing the number but still got the same error. There are six shapes and I tried them all.

Sorry,
Paul

 

[SkipVought]

What properties do you see, if you put this in a Watch Window?

objPPPres.Slides(intCnt).Shapes(4)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DevM]

Skip,
Sorry to take so long to reply I had a meeting. There are a lot of properties but most of them are blank or undefined. Here are a few that caught my eye:
AutoShapeType: msoShapeMixed
chart: <This member can only be accessed for a chart object>
has Chart: msoFalse
Name: "Object 4"

Should I be looking for something specific. The code seems to 'see' the slide it just won't let me access it.

Thanks again for helping,
Paul

 

[SkipVought]

Running Office 2007

I used your code to create an MS Graph object on a slide.

It HAS an OLEFormat object!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DevM]

Skip,
Thank you for all your help. But I have solved the problem. I stopped the program. Cut and pasted the object into Excel and used the Macro Recorder to see what was happening when I changed the x axis values. And there it was, plain as day, the new format for accessing shapes.

The line must now read:
Set pptGraph = objPPPres.Slides(intCnt).Shapes("Object 4").OLEFormat.Object

Can you believe that?!
So using the immediate window did help in showing me which object was which. Hopefully, this solution will help some other poor soul down the road.

Cheers,
Paul

 

[SkipVought]

You can reference objects in a collection EITHER by index number or by name. "Object 4" is not necessarily the 4th object in the collection.

BTW, the Watch Window would have helped you solve this in just a few seconds! faq707-4594


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]