Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save Embedded Objects (2002 SP3)

    I tried asking this in the VBA forum, but maybe it was the wrong place, so I thought I'd try here...

    A colleague asked me if it was possible to programatically step through all the embedded PowerPoint objects in an Excel Worksheet and save them as separate PowerPoint files.

    I've got as far as:

    Set ppObj = Worksheets("Sheet1").OLEObjects(1)
    ppObj.Activate
    With ppObj.Object.

    but can't figure out how to get a handle on the actual object to instruct it to saveas and then close.

    Any pointers?

    TIA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Save Embedded Objects (2002 SP3)

    I think you would have to copy them into a new powerpoint slide then save the powerpoint file.

    You would have to have VB open an instance of powerpoint with VB, but the code to work with powerpoint will have to come from someone who knows powerpoint. I only know excel VB and know nothing about the ppt VB model.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Embedded Objects (2002 SP3)

    The following code works for me, but it's not perfect - despite the Quit statement, PowerPoint remains in memory until the workbook is closed. The code will save embedded PowerPoint objects in C:Test as ExportedPresentation1.ppt, ExportedPresentation1.ppt etc.

    Sub ExportPPT()
    ' Change as needed
    Const strFile = "C:TestExportedPresentation"

    Dim i As Integer
    Dim wsh As Worksheet
    Dim obj As OLEObject
    Dim ppt As PowerPoint.Application

    Set wsh = ActiveSheet
    For i = 1 To wsh.OLEObjects.Count
    Set obj = wsh.OLEObjects(i)
    If Left(obj.progID, 10) = "PowerPoint" And obj.OLEType = xlOLEEmbed Then
    obj.Activate
    Set ppt = obj.Object.Application
    ppt.ActivePresentation.SaveAs strFile & i & ".ppt"
    ppt.Quit
    Set ppt = Nothing
    End If
    Next i

    Range("A1").Select

    Set obj = Nothing
    Set wsh = Nothing
    End Sub

  4. #4
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Embedded Objects (2002 SP3)

    Thanks Hans, this looks good. I must have been almost there but I missed the ActivePresentation.

    As a matter of interest, is there a good reason for using:

    For i = 1 To wsh.OLEObjects.Count
    Set obj = wsh.OLEObjects(i)

    As opposed to

    For each obj in wsh.OLEObjects

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Embedded Objects (2002 SP3)

    I use the i as a sequence number for the name of the exported file. If you have another method for naming the files, you can use the For Each construction.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Save Embedded Objects (2002 SP3)

    Hans,

    Out of curiosity I tried your code and it worked fine each time - Windows XP and Office 2002. I could find trace of a Powerpoint instance running on the termination of the code.

    Also, a variable could be incremented in each iteration of a For Each construction and the value used to save the file. Just a thought.


    Andrew

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Embedded Objects (2002 SP3)

    > Also, a variable could be incremented in each iteration of a For Each construction and the value used to save the file. Just a thought.

    Yes, that would perhaps be even better - you'd get consecutive numbers.

  8. #8
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Embedded Objects (2002 SP3)

    Well after posting my question about the variable i I realised that you used it for obtaining unique names! Just me being slow.

    Anyway, since each of the presentations already has a name, I altered your code slightly to use the existing name. I also removed the selection of A1. <font color=red>Was there a special reason for this?</font color=red>

    Now, unlike you and Andrew, when I run your code, I don't appear to have an instance of PowerPoint still running (at least I cannot see one in the Task Manager).

    One more question springs to mind. <font color=red>Is there anyway to have this running invisibly?</font color=red> Normally when one uses OLE, one can set the visible property of the application to False. But I can't seem to get this to work anywhere in your snippet???

    Here is what I have ended up with:

    <pre>Sub ExportPPT()
    Const strFile = "C:Test"

    Dim wsh As Worksheet
    Dim obj As OLEObject
    Dim ppt As PowerPoint.Application

    Set wsh = ActiveSheet
    For Each obj In wsh.OLEObjects
    If Left(obj.progID, 10) = "PowerPoint" And obj.OLEType = xlOLEEmbed Then
    obj.Activate
    Set ppt = obj.Object.Application
    ppt.ActivePresentation.SaveAs strFile & ppt.ActivePresentation.Name & ".ppt"
    ppt.Quit
    Set ppt = Nothing
    End If
    Next


    Set obj = Nothing
    Set wsh = Nothing
    End Sub
    </pre>


  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Embedded Objects (2002 SP3)

    The reason for selecting cell A1 was that when I ran the code without it, the last object was left activated and I had to click outside it manually to deactivate it.

    You might try setting Application.ScreenUpdating = False at the beginning of the procedure, and ... = True at the end. I haven't tested it.

  10. #10
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Embedded Objects (2002 SP3)

    Application.ScreenUpdating doesn't seem to work.

    As I say, normally with OLE, one sets the target application's Visible property to false.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Save Embedded Objects (2002 SP3)

    I fear I'm out of my depth here. The code I posted was assembled by trial and error. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  12. #12
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Embedded Objects (2002 SP3)

    Oh don't say that <img src=/S/artist.gif border=0 alt=artist width=34 height=29>. It was a work of art.

    I was just trying to put the icing on the cake!

    The code that you posted does the job excellently.

  13. #13
    New Lounger
    Join Date
    Dec 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save Embedded Objects (2002 SP3)

    Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>

    Hi, I am Pauls colleague that he helped - so thanks to all of you who assisted! I do have an extension to the problem but it is best posted in a new thread - <post#=437631>post 437631</post#>

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •