VBA In Powerpoint "The DoVerb Thingy"

Hello All,

I am trying to get some code to work that acts on an embedded worksheets
from powerpoint. With Steves help, it is mostly working. For some reason I
cannot get it to all come together. The code that follows is in a PPT with
several slides and some of the slides have msoEmbeddedOLEObjects (the
worksheets) and the second proceedure is called to act on the
msoEmbeddedOLEObjects. Can someone tell me where my errors are in this code?

Any help is greatly appreciated!


Sub Tag_n_Enumerate_Shapes()

Dim oSl As Slide
Dim oSh As Shape
Dim iShpaes As Integer
Dim iOLEShapes As Integer

For Each oSl In ActivePresentation.Slides
For Each oSh In oSl.Shapes
oSh.Tags.Add "TEST_TAG_NAME", "YadaYadaYada"
' You wanted a count of the shapes so:
iShapes = iShapes + 1
' Is it an OLEembedded thingie?
If oSh.Type = msoEmbeddedOLEObject Then
' Plug in Jon Peltiers's code here
' Increment the counter. Counts based on the if statement.
iOLEShapes = iOLEShapes + 1

'*********This is the code line I get the error on.***************

ActiveWindow.Selection.SlideRange.Shapes("Object 5").Select
ActiveWindow.Selection.ShapeRange.OLEFormat.DoVerb Index:=1
ActiveWindow.Selection.Unselect

'Call the ncmAgeCounter to do it's work
Application.Run "nmcAgeCounter"
End If
Next oSh
Next oSl

' and show the results:
MsgBox "There were " & CStr(i) & " shapes of which " _
& CStr(lOLEShapes) & " were OLE embedded objects."
End Sub

Sub nmcAgeCounter()

Dim briefDate As String
Dim lastCell

briefDate = InputBox("Please provide the date that this data will be
briefed." _
& Chr(10) & "format for the briefing date input is ""mm/dd/yyyy"".", _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)")
If briefDate = "" Then
MsgBox "You must provide valid date that" _
& Chr(10) & "is equal to or greater than todays date!" _
& Chr(10) & "This program will close. Please try again.", 16, _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)"
Exit Sub
ElseIf briefDate Date Then
MsgBox "You must provide valid date that" _
& Chr(10) & "is equal to or greater than todays date!" _
& Chr(10) & "This program will close. Please try again.", 16, _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)"
Exit Sub
End If

Set lastCell = Range("G65536").End(xlUp)

Columns("G:G").NumberFormat = "0"
Range("G5").FormulaR1C1 = "=IF(RC[-2]"""",DATE(2005,4,10)-RC[-2],"""")"
Range("G5").AutoFill Destination:=Range("G5", lastCell),
Type:=xlFillDefault
Range("A1").Select
End Sub


--
Thanks,

Patrick

See below ...


I am trying to get some code to work that acts on an embedded worksheets
from powerpoint. With Steves help, it is mostly working. For some reason I
cannot get it to all come together. The code that follows is in a PPT with
several slides and some of the slides have msoEmbeddedOLEObjects (the
worksheets) and the second proceedure is called to act on the
msoEmbeddedOLEObjects. Can someone tell me where my errors are in this code?

Any help is greatly appreciated!

Sub Tag_n_Enumerate_Shapes()

Dim oSl As Slide
Dim oSh As Shape
Dim iShpaes As Integer
Dim iOLEShapes As Integer

For Each oSl In ActivePresentation.Slides
For Each oSh In oSl.Shapes
oSh.Tags.Add "TEST_TAG_NAME", "YadaYadaYada"
' You wanted a count of the shapes so:
iShapes = iShapes + 1
' Is it an OLEembedded thingie?
If oSh.Type = msoEmbeddedOLEObject Then
' Plug in Jon Peltiers's code here
' Increment the counter. Counts based on the if statement.
iOLEShapes = iOLEShapes + 1

'*********This is the code line I get the error on.***************

ActiveWindow.Selection.SlideRange.Shapes("Object 5").Select
ActiveWindow.Selection.ShapeRange.OLEFormat.DoVerb Index:=1
ActiveWindow.Selection.Unselect

At this point, you may not have anything selected, so Select will give you an
error. It's more error-prone in general and slows things down. Best avoided
when possible. Try replacing the 3 lines above with:

oSh.OLEFormat.DoVerb Index:=1

Is nmcAgeCounter stored in the XLS? As written, it'd pretty much need to be.
Otherwise it'll take a bit more footwork to make it fly from w/in PPT.


'Call the ncmAgeCounter to do it's work
Application.Run "nmcAgeCounter"
End If
Next oSh
Next oSl

' and show the results:
MsgBox "There were " & CStr(i) & " shapes of which " _
& CStr(lOLEShapes) & " were OLE embedded objects."
End Sub

Sub nmcAgeCounter()

Dim briefDate As String
Dim lastCell

briefDate = InputBox("Please provide the date that this data will be
briefed." _
& Chr(10) & "format for the briefing date input is ""mm/dd/yyyy"".", _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)")
If briefDate = "" Then
MsgBox "You must provide valid date that" _
& Chr(10) & "is equal to or greater than todays date!" _
& Chr(10) & "This program will close. Please try again.", 16, _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)"
Exit Sub
ElseIf briefDate Date Then
MsgBox "You must provide valid date that" _
& Chr(10) & "is equal to or greater than todays date!" _
& Chr(10) & "This program will close. Please try again.", 16, _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)"
Exit Sub
End If

Set lastCell = Range("G65536").End(xlUp)

Columns("G:G").NumberFormat = "0"
Range("G5").FormulaR1C1 = "=IF(RC[-2]"""",DATE(2005,4,10)-RC[-2],"""")"
Range("G5").AutoFill Destination:=Range("G5", lastCell),
Type:=xlFillDefault
Range("A1").Select
End Sub


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

Hi Steve,
Yes, I was hoping to be able to store that second proceedure in PPT (the
same module as the first one). If I store it in Excel can it be in the
PERSONAL.xls workbook? Does PERSONAL.xls open when the msoEmbededOLEObect is
activated just as it opens in the background for ony other workbook?

Thanks,
Patrick

Hi Steve,
I tried running the code line you provided but get the following error :

Run-time error '-2147188160 (80048240)':

OLEFormat (unknown member) : Invalid request. The window must be in slide
or notes view.

Any ideas?

Thanks,
Patrick

In article , PSKelligan
wrote:
Hi Steve,
I tried running the code line you provided but get the following error :

Run-time error '-2147188160 (80048240)':

OLEFormat (unknown member) : Invalid request. The window must be in slide
or notes view.

Any ideas?

Not a one. But quote back the section of code we're talking about here and I
might. ;-)

Ah, wait. We're at the point where you're activating the OLE shape, right?
PPT would bark at you if you were in, say, Slide Sorter view at that point.
Your code will need to put it in slide view instead.

Try surrounding your code like so:

Dim lOriginalView as Long
' Remember the view you're in now
lOriginalView = ActiveWindow.ViewType
' Set PPT to Slide view
ActiveWindow.ViewType = ppViewSlide

' Do your stuff here

' Set the view back
ActiveWindow.ViewType = lOriginalView

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

In article , PSKelligan
wrote:
Hi Steve,
Yes, I was hoping to be able to store that second proceedure in PPT (the
same module as the first one).

You can do that but you'll need to study up some more on automating one app from
another. Jon's site should have some more on that.

I'd trust his examples over anything I'm likely to come up with off top of head.
g

If I store it in Excel can it be in the
PERSONAL.xls workbook? Does PERSONAL.xls open when the msoEmbededOLEObect is
activated just as it opens in the background for ony other workbook?

I don't know offhand. I'm not that familiar with Excel.
Try it. Drop

Msgbox("I tried it. Myself. It works.") in there and let it rock.


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

Hi Steve,
Ok Here is where I am at now. first here is my code to include some
attemps that I have commented out for debugging.

This first proceedure is in a PPT module:

Sub Tag_n_Enumerate_Shapes()

Dim oSl As Slide
Dim oSh As Shape
Dim iShapes As Long
Dim iOLEShapes As Long
Dim XLApp As Excel.Application
Dim lOriginalView As Long

' Remember the view you're in now
lOriginalView = ActiveWindow.ViewType
' Set PPT to Slide view
ActiveWindow.ViewType = ppViewSlide

For Each oSl In ActivePresentation.Slides
For Each oSh In oSl.Shapes
oSh.Tags.Add "TEST_TAG_NAME", "YadaYadaYada"
' You wanted a count of the shapes so:
iShapes = iShapes + 1
' Is it an OLEembedded thingie?
If oSh.Type = msoEmbeddedOLEObject Then
' Plug in Jon Peltiers's code here
' Increment the counter. Counts based on the if statement.
iOLEShapes = iOLEShapes + 1
' Can only paste into slide view
' Application.ActiveWindow.ViewType = ppViewSlide
' Activating the msoEmbeddedOLEObject
' Reference active slide
' Set PPSlide = ActivePresentation.Slides _
' (Application.ActiveWindow.Selection.SlideRange.Sli deIndex)

oSh.OLEFormat.DoVerb Index:=1

' Reference existing instance of Excel
Set XLApp = GetObject(, "Excel.Application")

'Call the ncmAgeCounter to do it's work
XLApp.Run "nmcAgeCounter"
End If
Next oSh
Next oSl
Set XLApp = Nothing

' Set the view back
ActiveWindow.ViewType = lOriginalView

' and show the results:
MsgBox "There were " & CStr(i) & " shapes of which " _
& CStr(lOLEShapes) & " were OLE embedded objects."
End Sub

This second proceedure is in both the PERSONAL.xls book (would not run from
there although PERSONAL.xls did open when the OLEObject was activated) and
the embedded msoEmbeddedOLEObject module. (Sometimes it starts up and
sometimes it does not but it never finishes):

Sub nmcAgeCounter()

Dim briefDate As String
Dim lastCl As Range

briefDate = InputBox("Please provide the date that this data will be
briefed." _
& Chr(10) & "format for the briefing date input is ""mm/dd/yyyy"".", _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)")
If briefDate = "" Then
MsgBox "Please provide valid date.", 16, _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)"
Exit Sub
ElseIf briefDate Date Then
MsgBox "You must provide valid date that" _
& Chr(10) & "is equal to or greater than todays date!" _
& Chr(10) & "This program will close. Please try again.", 16, _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)"
Exit Sub
End If

Set lastCl = Range("G65536").End(xlUp)

Columns("G:G").NumberFormat = "0"
Range("G5").FormulaR1C1 = "=IF(RC[-2]"""",DATE(2005,4,10)-RC[-2],"""")"
Range("G5").AutoFill Destination:=Range("G5", lastCl), Type:=xlFillDefault
Range("A1").Select
End Sub

As I step into this code I get the following error even with your code
ammendments regarding slide view.

Run-time error '-2147188160 (80048240)':
OLEFormat (unknown member) : Invalid request. The window must be in slide
or notes view.

If I check the actual view, I can see that you ammendments did take she
screen to slide view. If however I manually select the slide that holds my
first msoEmbededOLEObject, The code works on it but will give me the same
error as it tries to go to the next OLEObject on the next slide. Also the
Shape/OLEObject count is no longer working?? lol.

Thanks,
Patrick

In article , PSKelligan
wrote:
Hi Steve,
Ok Here is where I am at now. first here is my code to include some
attemps that I have commented out for debugging.

See comments inserted below:



This first proceedure is in a PPT module:

Sub Tag_n_Enumerate_Shapes()

Dim oSl As Slide
Dim oSh As Shape
Dim iShapes As Long
Dim iOLEShapes As Long
Dim XLApp As Excel.Application
Dim lOriginalView As Long

' Remember the view you're in now
lOriginalView = ActiveWindow.ViewType
' Set PPT to Slide view
ActiveWindow.ViewType = ppViewSlide

For Each oSl In ActivePresentation.Slides

' ADD THIS - should solve the problem with the error message
ActiveWindow.View.GoToSlide(oSl.SlideIndex)

For Each oSh In oSl.Shapes
oSh.Tags.Add "TEST_TAG_NAME", "YadaYadaYada"
' You wanted a count of the shapes so:
iShapes = iShapes + 1
' Is it an OLEembedded thingie?
If oSh.Type = msoEmbeddedOLEObject Then
' Plug in Jon Peltiers's code here
' Increment the counter. Counts based on the if statement.
iOLEShapes = iOLEShapes + 1
' Can only paste into slide view
' Application.ActiveWindow.ViewType = ppViewSlide
' Activating the msoEmbeddedOLEObject
' Reference active slide
' Set PPSlide = ActivePresentation.Slides _
' (Application.ActiveWindow.Selection.SlideRange.Sli deIndex)

' oSh.OLEFormat.DoVerb Index:=1

' Reference existing instance of Excel
' Set XLApp = GetObject(, "Excel.Application")

'Call the ncmAgeCounter to do it's work

' Rather than calling a procedure from w/in the XLS, I'd bring it all into PPT
' That might solve other problems as well
' XLApp.Run "nmcAgeCounter"
call nmcAgeCounter(osh)

End If
Next oSh
Next oSl
Set XLApp = Nothing

' Set the view back
ActiveWindow.ViewType = lOriginalView

' and show the results:
MsgBox "There were " & CStr(i) & " shapes of which " _
& CStr(lOLEShapes) & " were OLE embedded objects."
End Sub

This second proceedure is in both the PERSONAL.xls book (would not run from
there although PERSONAL.xls did open when the OLEObject was activated) and
the embedded msoEmbeddedOLEObject module. (Sometimes it starts up and
sometimes it does not but it never finishes):


You'll need to rewrite nmcAgeCounter in PPT.
See the section of Jon's page called:
Paste a Selected Excel Worksheet Range into the Active PowerPoint Slide
(PowerPoint VBA)
for more specifics


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

Hey Patrick,

In addition to my earlier reply, have another look he

Automate Excel from PowerPoint. Automate PowerPoint from Excel. And so on.
http://www.rdpslides.com/pptfaq/FAQ00368.htm

I've added a simple swat of example code that demos how you can activate an
embedded worksheet and retrieve data from it, all from w/in PPT.

You could modify the code to a sub that does something like this:

Sub DoWhatever(oSh as Shape)
Dim oWorkbook As Excel.Workbook
Dim oWorksheet As Excel.Worksheet
Dim oSh As Shape

Dim LastCol As Long
Dim LastRow As Long
Dim x As Long
Dim y As Long

Set oWorkbook = oSh.OLEFormat.Object
' Use the first sheet in the work book
Set oWorksheet = oWorkbook.worksheets(1)

' Get the last row/col
With oWorksheet
.Activate
' Find the extents of the data in the sheet
LastRow = .Range("a65535").End(xlUp).Row
LastCol = .Range("iv1").End(xlToLeft).Column

' Display the data
For x = 1 To LastRow
For y = 1 To LastCol
Debug.Print "Row" & CStr(x) & ":Col" & CStr(y) & " " & .Cells(x,
y)
Next
Next

End With

oWorkbook.Close (False)
Set oWorkbook = Nothing
Set oWorksheet = Nothing

End Sub

In article , PSKelligan
wrote:
Hi Steve,
Ok Here is where I am at now. first here is my code to include some
attemps that I have commented out for debugging.

This first proceedure is in a PPT module:

Sub Tag_n_Enumerate_Shapes()

Dim oSl As Slide
Dim oSh As Shape
Dim iShapes As Long
Dim iOLEShapes As Long
Dim XLApp As Excel.Application
Dim lOriginalView As Long

' Remember the view you're in now
lOriginalView = ActiveWindow.ViewType
' Set PPT to Slide view
ActiveWindow.ViewType = ppViewSlide

For Each oSl In ActivePresentation.Slides
For Each oSh In oSl.Shapes
oSh.Tags.Add "TEST_TAG_NAME", "YadaYadaYada"
' You wanted a count of the shapes so:
iShapes = iShapes + 1
' Is it an OLEembedded thingie?
If oSh.Type = msoEmbeddedOLEObject Then
' Plug in Jon Peltiers's code here
' Increment the counter. Counts based on the if statement.
iOLEShapes = iOLEShapes + 1
' Can only paste into slide view
' Application.ActiveWindow.ViewType = ppViewSlide
' Activating the msoEmbeddedOLEObject
' Reference active slide
' Set PPSlide = ActivePresentation.Slides _
' (Application.ActiveWindow.Selection.SlideRange.Sli deIndex)

oSh.OLEFormat.DoVerb Index:=1

' Reference existing instance of Excel
Set XLApp = GetObject(, "Excel.Application")

'Call the ncmAgeCounter to do it's work
XLApp.Run "nmcAgeCounter"
End If
Next oSh
Next oSl
Set XLApp = Nothing

' Set the view back
ActiveWindow.ViewType = lOriginalView

' and show the results:
MsgBox "There were " & CStr(i) & " shapes of which " _
& CStr(lOLEShapes) & " were OLE embedded objects."
End Sub

This second proceedure is in both the PERSONAL.xls book (would not run from
there although PERSONAL.xls did open when the OLEObject was activated) and
the embedded msoEmbeddedOLEObject module. (Sometimes it starts up and
sometimes it does not but it never finishes):

Sub nmcAgeCounter()

Dim briefDate As String
Dim lastCl As Range

briefDate = InputBox("Please provide the date that this data will be
briefed." _
& Chr(10) & "format for the briefing date input is ""mm/dd/yyyy"".", _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)")
If briefDate = "" Then
MsgBox "Please provide valid date.", 16, _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)"
Exit Sub
ElseIf briefDate Date Then
MsgBox "You must provide valid date that" _
& Chr(10) & "is equal to or greater than todays date!" _
& Chr(10) & "This program will close. Please try again.", 16, _
"NMC Age Counter (MTC TECHNOLOGIES Inc.)"
Exit Sub
End If

Set lastCl = Range("G65536").End(xlUp)

Columns("G:G").NumberFormat = "0"
Range("G5").FormulaR1C1 = "=IF(RC[-2]"""",DATE(2005,4,10)-RC[-2],"""")"
Range("G5").AutoFill Destination:=Range("G5", lastCl), Type:=xlFillDefault
Range("A1").Select
End Sub

As I step into this code I get the following error even with your code
ammendments regarding slide view.

Run-time error '-2147188160 (80048240)':
OLEFormat (unknown member) : Invalid request. The window must be in slide
or notes view.

If I check the actual view, I can see that you ammendments did take she
screen to slide view. If however I manually select the slide that holds my
first msoEmbededOLEObject, The code works on it but will give me the same
error as it tries to go to the next OLEObject on the next slide. Also the
Shape/OLEObject count is no longer working?? lol.

Thanks,
Patrick


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

This worked great!


' ADD THIS - should solve the problem with the error message
ActiveWindow.View.GoToSlide(oSl.SlideIndex)