If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 ================================================ |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 ================================================ |
#6
|
|||
|
|||
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 ================================================ |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 ================================================ |
#9
|
|||
|
|||
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 ================================================ |
#10
|
|||
|
|||
This worked great!
' ADD THIS - should solve the problem with the error message ActiveWindow.View.GoToSlide(oSl.SlideIndex) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Linking Several Charts to a PowerPoint Presentation | JRPK | Links and Linking | 3 | April 2nd, 2005 10:02 PM |
How to put powerpoint inside of powerpoint? (picture in picture?) | Jwolfer | Powerpoint | 5 | January 28th, 2005 11:37 PM |
vb.net dll won't release powerpoint | C Williams | Powerpoint | 6 | January 3rd, 2005 07:07 PM |
open Powerpoint main window and display a PPT file with automation | David | Powerpoint | 0 | December 6th, 2004 08:57 PM |
PowerPoint to Excel link issues | DrLostinExcel | Powerpoint | 5 | November 19th, 2004 03:33 PM |