Copying the text of an embedded Word object into the original document

Question

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:

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

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

Answer 1

You did almost.

As soon you opened the embedded word object you can get the proeprty from the OLEFormat.Object itself

Sub findEmbed()
Dim oSh As InlineShape
Dim obj As Word.Document
For Each oSh In ActiveDocument.InlineShapes
    If oSh.Type = wdInlineShapeEmbeddedOLEObject Then
        oSh.OLEFormat.Activate
        Set obj = oSh.OLEFormat.Object
        obj.Content.Copy
        
        Windows(2).Selection.PasteAndFormat (wdPasteDefault)
        Windows(1).Close False
    End If
Next oSh
End Sub

When word opened for activate it is Windows (1) and the parent is Windows(2).I have used that logic.I am not sure but windows(1).Selection can give you the same

I used content for copying entire thing of embedded doc.

Pls check and let us know...

---

Best Regards,
Asadulla Javed, Kolkata
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

Answer 2

Thanks for your reply!

Unfortunately, with your code I'm getting an error at line "Set obj = oSh.OLEFormat.object." It's 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 oSh.objOLE.object an Object. Or something.

Any thoughts? Could it be that I don't have the right reference libraries selected?

Thanks!

Answer 3

I assumed you run the code in Word VBA.Then it is not required to set any reference to Word.Object Library.

I suppose in the embedded objects,some does not support com automation( Does not expose any object model.)So we should check for all shp if it is Word then go for next statement...

Sub findEmbed()
Dim oSh As InlineShape
Dim obj As Document
For Each oSh In ActiveDocument.InlineShapes
    If oSh.Type = wdInlineShapeEmbeddedOLEObject Then
       If Left(TypeName(oSh.OLEFormat.Object), 8) = "Document" Then
            oSh.OLEFormat.Activate
            Set obj = oSh.OLEFormat.Object
            obj.Content.Copy
        
            Windows(2).Selection.PasteAndFormat (wdPasteDefault)
            Windows(1).Close False
        End If
    End If
Next oSh
End Sub

---

Best Regards,
Asadulla Javed, Kolkata
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

Answer 4

It's still giving me the same error at "If Left(TypeName(oSh.OLEFormat.Object), 8) = "Document" Then"

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

Thanks!

Answer 5

Actually I should use ProgId because all embedded object will not support Object method.See if it helps.

Sub findEmbed()
Dim oSh As InlineShape
Dim obj As Document
For Each oSh In ActiveDocument.InlineShapes
    If oSh.Type = wdInlineShapeEmbeddedOLEObject Then
       If oSh.OLEFormat.ProgID Like "*Word.Docum*" Then
            oSh.OLEFormat.Activate
            Set obj = oSh.OLEFormat.Object
            obj.Content.Copy
        
            Windows(2).Selection.PasteAndFormat (wdPasteDefault)
            Windows(1).Close False
        End If
    End If
Next oSh
End Sub

---

Best Regards,
Asadulla Javed, Kolkata
---------------------------------------------------------------------------------------------
Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.