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

J

Josh Sale

Guest
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?

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

Regards,
Shyam Pillai

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



"Josh Sale" <jsale@tril dot cod> wrote in message
news:utJLVqTsIHA.4840@TK2MSFTNGP05.phx.gbl...
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

 
Comments interleaved below:

> 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.

My guess is that you're in Slide Show view. You can't select anything in slide
show view, manually or via code, so your .Select will fail.

If you're not in slide show view, try something along these lines:

If oSh.Type = msoEmbeddedOLEObject Then
oSh.OLEFormat.Activate
MsgBox "Activated" ' or whatever you want
oSh.OLEFormat.Object.Application.Quit
End If

> 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?

Ah, good question. Like so:

Dim oRng As ShapeRange

' save the current selection
' (do some testing first to make sure something's selected)
Set oRng = ActiveWindow.Selection.ShapeRange

' unselect the current selection
ActiveWindow.Selection.Unselect

' do your stuff here, then ...

' re-select whatever was selected before
oRng.Select

> 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?

You? Nothing.
PowerPoint? Lots of nice little touches Excel has. ;-)

PowerPoint won't run code as the result of opening a file.
You'd need to write an event handler and have it in a loaded add-in, trap the
open event there and somehow determine whether this particular presentation
needs some further action.

Make PPT respond to events
http://www.pptfaq.com/FAQ00004.htm

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================


 
Thanks Shyam!

Your GotoSlide suggestion worked perfectly!!

Thanks.

josh




"Shyam Pillai" <ShyamPillai@Gmail.com> wrote in message
news:%23KtvmpUsIHA.1772@TK2MSFTNGP03.phx.gbl...
> 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?
>
> 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
>
> Regards,
> Shyam Pillai
>
> Image Importer Wizard
> http://skp.mvps.org/iiw.htm
>
>
>
> "Josh Sale" <jsale@tril dot cod> wrote in message
> news:utJLVqTsIHA.4840@TK2MSFTNGP05.phx.gbl...
> 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





"Steve Rindsberg" <abuse@localhost.com> wrote in message
news:VA.00003f84.03beb36d@localhost.com...
> Comments interleaved below:
>
>> 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.
>
> My guess is that you're in Slide Show view. You can't select anything in
> slide
> show view, manually or via code, so your .Select will fail.
>
> If you're not in slide show view, try something along these lines:
>
> If oSh.Type = msoEmbeddedOLEObject Then
> oSh.OLEFormat.Activate
> MsgBox "Activated" ' or whatever you want
> oSh.OLEFormat.Object.Application.Quit
> End If
>
>> 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?
>
> Ah, good question. Like so:
>
> Dim oRng As ShapeRange
>
> ' save the current selection
> ' (do some testing first to make sure something's selected)
> Set oRng = ActiveWindow.Selection.ShapeRange
>
> ' unselect the current selection
> ActiveWindow.Selection.Unselect
>
> ' do your stuff here, then ...
>
> ' re-select whatever was selected before
> oRng.Select
>
>> 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?
>
> You? Nothing.
> PowerPoint? Lots of nice little touches Excel has. ;-)
>
> PowerPoint won't run code as the result of opening a file.
> You'd need to write an event handler and have it in a loaded add-in, trap
> the
> open event there and somehow determine whether this particular
> presentation
> needs some further action.
>
> Make PPT respond to events
> http://www.pptfaq.com/FAQ00004.htm
>
> -----------------------------------------
> Steve Rindsberg, PPT MVP
> PPT FAQ: www.pptfaq.com
> PPTools: www.pptools.com
> ================================================
>
>


 

Back
Top