7

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,

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Dev.K.
  • 2,428
  • 5
  • 35
  • 49

3 Answers3

9

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
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • Thanks! What about MS Word document. Can I do similar stuff with MS Word document Macro as well ? – Dev.K. Dec 27 '15 at 10:24
  • 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`. But there is no `MS-Word` tag in your Question and nowhere Word is mentioned. So why you are asking for Word now? – Axel Richter Dec 27 '15 at 11:08
  • Sorry about that, But I got what I wanted to know. Thanks a lot. – Dev.K. Dec 27 '15 at 12:26
  • Just wanted to add that if you are working with embedded Office files, it is possible to access them with `OLEObject.Object` property once you've opened them. I'm pretty sure you can do this with all other document types, like PDFs, but that would require separate libraries to be installed. – Kirill Tkachenko Mar 11 '21 at 08:49
4

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
Andrew L.
  • 111
  • 1
  • 11
  • What should be the object for MS Word. In excel I got the object name by recording the operation. but office doesn't allow me to record the object clicking operation.? – Dev.K. Dec 27 '15 at 12:33
  • There is a collection of InlineShapes and you can refer specified objet via it's number. From what I understand this numeration goes top-down and left-right. You can iterate through the objects you have in the file with below code - type of the object you want is 1: `cnt = ActiveDocument.InlineShapes.Count For i = 1 To cnt MsgBox ActiveDocument.InlineShapes(i).Type Next` – Andrew L. Dec 27 '15 at 12:57
  • From what I see there is no .Name property as we get it in Excel with OLEObjects collection. – Andrew L. Dec 27 '15 at 13:10
-1

Try this:

Sub test()
    With ActiveSheet
        Set o = .OLEObjects("Objekt 1")
        o.Verb xlPrimary
    End With
End Sub
SandPiper
  • 2,816
  • 5
  • 30
  • 52
aissaoui djamel
  • 1