How do I copy from an embedded word object with a vba macro?

I've written a macro in an Excel file that opens an external Dord document (external.docx) and copies the contents (so they can be pasted into a new word file, along with data from the Excel file).

Instead of having an external Word file, I'd like to have the Word document as an embedded object in the main Excel file. The problem is I don't know how to get the macro to copy from an embedded file instead of an external one.

Currently, the segment of code that opens the document and copies the contents is this:

Dim appWrdExt As Object
Dim ExtDoc As Object

Set appWrdExt = CreateObject("Word.Application")

Set ExtDoc = appWrdH.Documents.Open("C:\external.docx") ' This is the line I think I need to modify

appWrdExt.Selection.WholeStory
appWrdExt.Selection.Copy

I created an embedded object from the external word document. If I record a macro, and open the object, it generates this code:

ActiveSheet.Shapes("Object 375").Select
Selection.Verb Verb:=xlOpen

How do I reconcile that with my existing macro so that I can copy the contents from the embedded document?

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Answer

Andreas Killer

Well, the OLEobject has many properties, I can not say which one is the best for you.

BTW, did you know how to explore the properties of a variable? Execute this in your file:

Sub Test2()
Dim Oo As OLEObject
Set Oo = ActiveSheet.OLEObjects(1)
Stop
End Sub

If the code stops, select Oo, press Shift-F9 and click Add. The watch window opens, click at the + on the left side.

As you see the simplest one would be to compare the name property, this name is shown also in the namefield in the Excel GUI and could be changed there. You can also change it with VBA: Oo.Name = "Basic List"

Another way is to determine which cells the object overlaps, every object in a sheet has a TopLeftCell and BottomRightCell property, both returns a range object.

Sub Test3()
Dim Oo As OLEObject
Dim R As Range
Set Oo = ActiveSheet.OLEObjects(1)
Set R = Range(Oo.TopLeftCell, Oo.BottomRightCell)
MsgBox R.Address
End Sub

With these properties, you could even find out the order in which the objects are arranged. Or which is the closest to a specific cell.

Andreas.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer

Andreas Killer

Sub Test()
Dim Oo As OLEObject
Dim wDoc As Object 'Word.Document

'Search for the embedded Word document
For Each Oo In ActiveSheet.OLEObjects
    If InStr(1, Oo.progID, "Word.Document", vbTextCompare) > 0 Then
      'Open the embedded document
      Oo.Verb xlVerbPrimary
      'Get the document inside
      Set wDoc = Oo.Object

      'Copy the contents to cell A1
      wDoc.Content.Copy
      Range("A1").PasteSpecial xlPasteValues

      'Select any cell to close the document
      Range("A1").Select
      'Done
      Exit For
    End If
Next
End Sub

Contribute to the Microsoft 365 and Office forum!

Excel Forum Top Contributors:

How do I copy from an embedded word object with a vba macro?

Replies (4)

Question Info

How do I copy from an embedded word object with a vba macro?

Replies (4) 

Question Info

Replies (4)