Need to create a button in Excel that will copy the text out of an embedded Word document

Question

Background: I created a Dashboard that has VBA buttons that copy text from a cell into the clipboard so that I can paste the text (which is a template) into my jobs work management system. Because of an update I can now past formatted text into my work system. Unfortunately if I just copy the text in a cell it will lose its format when pasting (i.e. text in cell is bold and I copy it, the text will paste unformatted). I have found if I copy and past from a word doc into my work system the formatting is preserved.

I need a way to click a button and tell excel to copy the text out of embedded word doc "object 1" for example.

Because of my work management system, the embedded word doc is the only way I can copy my template with its bullet points and have it format correctly when I paste.

In the example below, the embedded word doc is object 1 on the spreadsheet and listed as =EMBED("Word.Document.12","") in the forumula bar.

Private Sub CommandButton1_Click()

    Dim objWord As Object
    Dim objDoc As Object
    Dim strText As String
    
    'Get the Word application object
    On Error Resume Next
    Set objWord = GetObject(, "Word.Application")
    If objWord Is Nothing Then
        Set objWord = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    
    'Select the Word document
    Set objDoc = objWord.ActiveDocument.Shapes("object 1").OLEFormat.Object
    If objDoc Is Nothing Then
        MsgBox "The embedded Word document 'word1' was not found."
        Exit Sub
    End If
    
    'Copy the text to the clipboard
    strText = objDoc.Content.Text
    If strText <> "" Then
        Clipboard.SetText strText
    End If
    
    'Clean up
    Set objDoc = Nothing
    Set objWord = Nothing
End Sub

Answer 1

EDIT - improved for performance. The first time you access the embedded document it creates a hidden Word instance which you can see in Task Manager - this appears to stick around until the workbook is closed. So there's no need to call oleObj.Verb xlVerbPrimary after the first access - you can then just access the Document object directly.

Sub Tester()
    CopyWordText Sheet2.OLEObjects("object 1")
End Sub

Sub CopyWordText(oleObj As Object)
    
    Dim ok As Boolean
    
    On Error Resume Next
    oleObj.Object.Range.Copy  'if the object has previously been activated this will work
    ok = Err.Number = 0
    On Error GoTo 0
    
    If Not ok Then  'no go - need to first activate the document
        Debug.Print "activating", Now
        oleObj.Verb xlVerbPrimary
        oleObj.Object.Range.Copy 'now we can copy...
    End If
   
End Sub