Opening a Worksheet object embedded in a PowerPoint presentation

[JimHol]

Hi All,

I am attempting to open a worksheet object that is embedded in a PowerPoint presentation. The code below finds and selects the embedded object fine the problem lies in my syntax to open the document. anyone have any thoughts?

         ThisEmbdName = Shape.Name
         Shape.Select
                                
         If Shape.Type = Office.MsoShapeType.msoEmbeddedOLEObject Then
               ThatType = Shape.OLEFormat.progID
               If InStr(ThatType, "Microsoft Excel") > 0 Or InStr(ThatType, "Microsoft Word ") > 0 _
                   Or InStr(ThatType, "Microsoft PowerPoint") > 0 Or InStr(ThatType, "Adobe Acrobat") > 0 _
                   Or InStr(ThatType, "Chart") > 0 Or InStr(ThatType, "Word.Document") > 0 _
                   Or InStr(ThatType, "Excel.Sheet") > 0 Then
                                        
                       Set Embd = CreateObject(Class:="Excel.Sheet")

                      'Open the Document
                       Embd.Worksheet.Open Filename:=ThisEmbdName
              End If
         End If

Thanks,
Jim

 

[JimHol]

Kind of getting a little desperate. Anyone have any suggestions or a starting point?

 

[RoryA]

Normally just activating the embedded object will open it.

 

[JimHol]

Really! What about saving it as a separate file?

 

[RoryA]

I don't know how it works in PowerPoint to be honest, as I never use it. I'll test when I get a chance.

 

[JimHol]

It may also be enough to just determine what application the embedded file opened in is that possible int VBA? The embedded files can be in Excel, Word or Powerpoint.

 

[JimHol]

The more I look into this issue the more I am thinking that the approach I am taking may require 2 separate macros, one in Excel to open and cycle through the PowerPoints and one in PowerPoint to cycle through the slides to open and try to save the embedded items. Everything I have tried seams to imply that the presentation needs to be active to open an embedded object. This leaves me with two questions to refine/redirect my approach:

1) Is possible to open an embedded item in PowerPoint, attempt to save it, from an Excel macro without the PowerPoint being active? Keeping in mind that the presentation is opened by the Excel macro.

2) How complicated would it be to use the two macro approach? I would need an Excel macro to open the PowerPoint, which I have now, then call the PowerPoint macro to run through the slides looking for embedded items, attempt to save, and pass the results back to the Excel sheet.

Any comments\recommendations?

Thanks,
Jim

 

[mooseman]

1) Is possible to open an embedded item in PowerPoint - Yes
2) attempt to save it - not sure what you mean. do you mean save the embedded object as an excel file? I do not think so.
3) from an Excel macro without the PowerPoint being active - Not that I have found

Why not write a powerpoint macro that finds the shape
(you seem to have that), activate it and call an Excel macro to do whatever you need to do.
Use .OLEFormat.Activate once you have the shape
Call Excel_Macro
ActiveWindow.Selection.Unselect ' this deselects the embedded object

sub Excel_Macro ()
Dim oWB As Excel.Workbook ' Excel Workbook Object

On Error GoTo Err_PPXL

'Get Excel file as object
Set oWB = GetObject("C:\Foldername\excelfilewithmacro.xlsm")

'Call the Excel Macro
oWB.Application.Run "'excelfilewithmacro.xlsm'Modulename.Macroname"

' Release Objects - Good Practive
If Not oWB Is Nothing Then Set oWB = Nothing

Err_PPXL:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear
End If
End Sub

Hope this helps.