Shape (unknown member) : Invalidid request. To select a shape, its view must be active.

  • Thread starter Thread starter Josh Sale
  • Start date Start date
J

Josh Sale

Hi. This is my first attempt to write a PowerPoint macro though I've written lots of Excel macros.

I have a few questions:

First, I'm trying to write a simple macro that will loop through all of the slides in the open PowerPoint presentation and identify each embedded Excel worksheet and activate it (the reason for this isn't important but the worksheet is connected to a database and I want to get fresh data). My macro looks like this:

Sub UpdateExcelShapes()
Dim oShape As Shape
Dim oPresentation As Presentation
Dim oSlide As Slide
Dim oSelection As Selection

Set oPresentation = ActivePresentation
Set oSelection = Application.ActiveWindow.Selection

For Each oSlide In oPresentation.Slides
For Each oShape In oSlide.Shapes
If oShape.Type = msoEmbeddedOLEObject Then
If Left(oShape.OLEFormat.ProgID, 11) = "Excel.Sheet" Then
oSlide.Select
oShape.Select
oShape.OLEFormat.Activate
SendKeys "{ESC}"
End If
End If
Next oShape
Next oSlide

' If Not oSelection Is Nothing Then
' oSelection.Select
' End If
End Sub

When the macro gets to the oShape.Select statement I get the error message in the subject line of this posting. What does it mean and how do I fix it? I have no idea what view the message is referring to.

Second, I've written a similar little macro for Word. In that macro I needed the moral equivalent of the commented out code at the bottom of the macro to reselect whatever was originally selected before the macro started. Sadly in PowerPoint the Selection object doesn't have a Select method. Any suggestions?

Thirdly, if I can get this macro to work, I'd like to have it triggered whenever the PowerPoint presentation is opened. I was looking for the equivalent of the ThisWorkBook object that would have a Open event I could hook into but I see no such object in the VBE project explorer window. What am I missing?

TIA,

josh
 
Josh,
Use ActiveWindow.View.GotoSlide oSlide.SlideIndex to switch to that view in
which you can select the shape.
Second, I've written a similar little macro for Word. In that macro I
needed the moral equivalent of the commented out code at the bottom of the
macro to reselect whatever was originally selected before the macro
started. Sadly in PowerPoint the Selection object doesn't have a Select
method. Any suggestions?
Click to expand...

In PowerPoint, you would store the current view type and check what type of
selection (Text/Shapes or slides) and store that info and then reset it back
when your macro completes running.
for the third part you will need to create an eventhandler. There is not
automatic event in PowerPoint when a presentation is opened. Take a look
at the event handler demo on this page: http://skp.mvps.org/download.htm
Click to expand...

Regards,
Shyam Pillai

Image Importer Wizard
http://skp.mvps.org/iiw.htm



Hi. This is my first attempt to write a PowerPoint macro though I've
written lots of Excel macros.

I have a few questions:

First, I'm trying to write a simple macro that will loop through all of the
slides in the open PowerPoint presentation and identify each embedded Excel
worksheet and activate it (the reason for this isn't important but the
worksheet is connected to a database and I want to get fresh data). My
macro looks like this:

Sub UpdateExcelShapes()
Dim oShape As Shape
Dim oPresentation As Presentation
Dim oSlide As Slide
Dim oSelection As Selection

Set oPresentation = ActivePresentation
Set oSelection = Application.ActiveWindow.Selection

For Each oSlide In oPresentation.Slides
For Each oShape In oSlide.Shapes
If oShape.Type = msoEmbeddedOLEObject Then
If Left(oShape.OLEFormat.ProgID, 11) = "Excel.Sheet" Then
oSlide.Select
oShape.Select
oShape.OLEFormat.Activate
SendKeys "{ESC}"
End If
End If
Next oShape
Next oSlide

' If Not oSelection Is Nothing Then
' oSelection.Select
' End If
End Sub

When the macro gets to the oShape.Select statement I get the error message
in the subject line of this posting. What does it mean and how do I fix it?
I have no idea what view the message is referring to.

Second, I've written a similar little macro for Word. In that macro I
needed the moral equivalent of the commented out code at the bottom of the
macro to reselect whatever was originally selected before the macro started.
Sadly in PowerPoint the Selection object doesn't have a Select method. Any
suggestions?

Thirdly, if I can get this macro to work, I'd like to have it triggered
whenever the PowerPoint presentation is opened. I was looking for the
equivalent of the ThisWorkBook object that would have a Open event I could
hook into but I see no such object in the VBE project explorer window. What
am I missing?

TIA,

josh
 
Hi Steve.

Shyam's post had a perfect solution for the view problem and your suggestion
for handling the saving and restoration of the current selection worked
great. Thank you.

In my sniffing around the PowerPoint help files I had found the Application
events but they really didn't do it for me. I'm just not interested enough
in this problem to create a PowerPoint add-in that will trap the document
open events and then call the macro that you and Shyam helped me with. I'll
tell the user they will have to put a button in their document that calls
the macro.

I guess I've gotten spoiled working in the Excel environment where its so
easy to create these document specific functions!

Thanks again.

josh
 
Back
Top