Open an Embedded Object in Excel using VBA

Question

In an ms office document I've embedded / inserted an external document (object) (PDF in my case).

After opening the document, when I click on the PDF object icon, It opens up the PDF file embedded in it.

Using VBA / Macro I want to do the same thing, Where I'll have to run a macro and it will open up the embedded PDF file(Without clicking on the PDF ICON).

Is it possible?

Thanks,

Answer 1

Excel:

You can get the OLEObject form the OLEObjects of the Worksheet. See OLEObjects - https://msdn.microsoft.com/en-us/library/office/ff840244.aspx, OLEObject - https://msdn.microsoft.com/en-us/library/office/ff838421.aspx, OLEObject members - https://msdn.microsoft.com/EN-US/library/office/ff841208.aspx.

There is a method Verb which has a verb for opening the object. See https://msdn.microsoft.com/EN-US/library/office/ff838827.aspx - Verbs - https://msdn.microsoft.com/EN-US/library/office/ff820926.aspx

Example:

Sub test()
 With ActiveSheet
  Set o = .OLEObjects("Objekt 1")
  o.Verb xlVerbOpen
 End With
End Sub

"Objekt 1" is the name of the object in the Excel worksheet. The object must be in the active sheet.

Word:

In Word it depends on if the embedded object is in an InlineShape or an Shape. And there is no OLEObjects collection. So you must handle with Shape.OLEFormat. See InlineShapes - https://msdn.microsoft.com/en-us/library/office/ff822592.aspx, Shapes - https://msdn.microsoft.com/en-us/library/office/ff845240.aspx, Shape - https://msdn.microsoft.com/en-us/library/office/ff196943.aspx, OLEFormat - https://msdn.microsoft.com/EN-US/library/office/ff197153.aspx.

Example:

Sub test()

 With ActiveDocument
  Set oShape = .InlineShapes(1) 'The embedded object is the first InlineShape.
  'Set oShape = .Shapes(1) 'The embedded object is the first Shape.
  Set oOLEFormat = oShape.OLEFormat
  oOLEFormat.Open
 End With

End Sub

Answer 2

In short, when you already know which object you are referring to:

Excel

Sheets("Sheet1").OLEObjects("Object 1").Activate

Word

ActiveDocument.InlineShapes(1).OLEFormat.Open