Save Embedded Objects (2002 SP3)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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. Was there a special reason for this?

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. Is there anyway to have this running invisibly? 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:

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

Reply | Quote

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.

Reply | Quote

Application.ScreenUpdating doesn’t seem to work.

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

Reply | Quote

I fear I’m out of my depth here. The code I posted was assembled by trial and error.

Reply | Quote

Oh don’t say that . 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.

Reply | Quote

Edited by HansV to provide link to post – see Help 19

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

Thanks!

Reply | Quote

Oh don’t say that . 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.

Reply | Quote

I fear I’m out of my depth here. The code I posted was assembled by trial and error.

Reply | Quote

Application.ScreenUpdating doesn’t seem to work.

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

Reply | Quote

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.

Reply | Quote

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. Was there a special reason for this?

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. Is there anyway to have this running invisibly? 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:

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote