Opening a Word object embedded in a Word document

[mroyt]

Hi all,

I am writing a VBA Word macro in Office 2007 that will reformat a long document that contains many exported emails about a certain topic. One problem is that attachments get exported as is, and are embedded in the document as objects.

I want to write a subroutine that will:

[ol 1]
[li]Scan the document for embedded objects (they will almost always be Word documents; very rarely Excel)[/li]
[li]Open each attachment[/li]
[li]Copy the contents[/li]
[li]Close the attachment[/li]
[li]Go back to the original document, and replace the object with the actual text[/li]
[/ol]

I'm entirely self-taught, and everything I have is based on recording-reading and online research. Here's what I have so far, followed by the present (perhaps last) sticking point:


Sub findEmbed()

Dim oSh As InlineShape

For Each oSh In ActiveDocument.InlineShapes
If oSh.Type = wdInlineShapeEmbeddedOLEObject Then
'This is here because, when activating the object, I don't know how to bypass the dialog box asking what to do with the file
MsgBox ("Please select ""Open"" in the following dialog box")
oSh.OLEFormat.Activate
'The issue arises here
ActiveWindow.Selection.WholeStory
Selection.Copy
Debug.Print (oSh.OLEFormat.IconPath)
ActiveWindow.Close
Windows("homeDocument").Activate
Selection.PasteAndFormat (wdPasteDefault)

The problem is that when I activate the object, it opens as a new instance of Word, and it does not become the active window. So it's actually the original document that ends up getting selected and copied.

I guess my main question is: How do I get the newly-opened object to be the active window so that I can select it?

Eager to hear your ingenious responses!

-Michael

 

[macropod]

Try something based on:

Sub Demo()
Dim iShp As InlineShape, Rng As Range
Dim objOLE As OLEFormat, objWrd As Object
For Each iShp In ActiveDocument.InlineShapes
  With iShp
    If .Type = wdInlineShapeEmbeddedOLEObject Then
      If .OLEFormat.Application = "Microsoft Word" Then
        Set objOLE = .OLEFormat
        objOLE.Activate
        Set objWrd = objOLE.Object
        objWrd.Range.Copy
        objWrd.Close , False
        ' Deactivate object
        SendKeys "{esc}", Wait:=True
        Set Rng = .Range
        .Delete
        Rng.Paste
      End If
    End If
  End With
Next
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[mroyt]

Thanks for the reply, Paul!

Unfortunately, with your code, the attachment is still opening in a separate instance of Word. Then, at line "Set objWrd = objOLE.object" it's throwing the following error: "Run-time error '430': Class does not support Automation or does not support expected interface" Not sure what that means, exactly, but it doesn't seem to want to call iShp.objOLE.object an Object. Or something. Any thoughts?

I thought of a workaround, though not sure how to do it. If there were a way to figure out the name of the opened attached, I could "manually" activate the window using Windows().Activate. Alternatively, if there were a way to automate a SaveAs function with the object, I could save it as a file with a known name and filepath, and then open it from the macro. There are likely other possibilities as well...

Thanks again!

 

[macropod]

The code works fine for me in both Word 2003 and 2010.

As you observed, the code opens the object. That is necessary if you want to extract its content. You cannot use Windows().Activate as, until the OLEobject is activated, it's just a picture. The activated object is in the same instance of Word, however, which is why the code uses Sendkeys to exit it rather than, say, '.OLEFormat.Object.Application.Quit' or 'objWrd.Application.Quit'.

Cheers
Paul Edstein
[MS MVP - Word]

 

[mroyt]

Hi Paul,

Thanks for the feedback. Any idea why I'm getting that error (Class does not support Automation or does not support expected interface)? Could it be that I don't have the right references selected?

Currently I have:
Visual Basic for Applications
Microsoft Word 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft Excel 12.0 Object Library
Microsoft Forms 12.0 Object Library

Thanks,
Michael

 

[macropod]

Hi Michael,

You shouldn't need the Excel or Forms references, but I wouldn't have thought they'd make any difference.

Of course, there may also be a problem with your Office installation, in which case, try repairing it (via Word Options|Resources|Diagnose).

Cheers
Paul Edstein
[MS MVP - Word]

 

[mroyt]

Hi Paul,

I ran a diagnostic and it found no problems with my Office installation. Since you say it worked on Word 2003 and 2010, and I'm using 2007, could that be part of the problem?

It seems like there are some automation limitations on the InlineShape class. Do you have any workarounds or thoughts on what could be going wrong?

Thanks!

 

[macropod]

If the same code works for Word 2003 & 2010, it is very unlikely it wouldn't work with 2007.

There are various settings that repairing Word won't fix, so go to

http://support.microsoft.com/kb/921541

and follow the steps outlined there.

Cheers
Paul Edstein
[MS MVP - Word]