Clearing/deleting text in an Embedded Word Document (Excel 2016)

[tjamestx]

Have an Excel Template with an Embedded Word Document, so user can type a description of work to be done easily. I was wondering if anyone could tell me if VBA could be used to clear or delete the text typed into the document on either BeforeClose or Open? That way when the Template is used again the document is clean. Just need to remove all text in the document no parameters of any kind need to be set. Thank you in advance for any help.

 

[Domenic]

You can call the following procedure from the BeforeClose event handler. Change the references to the worksheet and the embedded object accordingly.

Sub ClearContentsFromEmbeddedWordDocument()

    Dim objOLE As OLEObject
    Set objOLE = ThisWorkbook.Worksheets("Sheet1").OLEObjects("Object 1") 'change the names of the worksheet and embedded Word object
    
    objOLE.Verb xlVerbOpen

    Dim objWord As Object
    Set objWord = objOLE.Object
    
    With objWord
        .Application.Selection.WholeStory
        .Application.Selection.Delete
        .Parent.Quit
    End With
    
End Sub

Hope this helps!

 

[tjamestx]

Domenic, Thank you for the reply. I several meetings this morning but, I will try this out and reply back on the outcome...

 

[tjamestx]

Domenic, This worked perfectly! I can't thank you enough! Another question if you have the time. I have other Word Documents embedded in other Worksheets of the same Workbook. If I needed to clear those as will, would it just be a matter of copying the code a second time and changing the sheet name and object name? Sorry for being such a novice about this...

 

[Domenic]

The following macro will loop through each worksheet within the workbook running this code, and clear the contents of any and all embedded Word documents...

Sub ClearContentsFromAllEmbeddedWordDocuments()

    Dim ws As Worksheet
    Dim objOLE As OLEObject
    Dim objWord As Object
    
    For Each ws In ThisWorkbook.Worksheets
        For Each objOLE In ws.OLEObjects
            If objOLE.OLEType = xlOLEEmbed Then
                If Left(objOLE.progID, 13) = "Word.Document" Then
                    objOLE.Verb xlVerbOpen
                    Set objWord = objOLE.Object
                    With objWord
                        .Application.Selection.WholeStory
                        .Application.Selection.Delete
                        .Parent.Quit
                    End With
                End If
            End If
        Next objOLE
    Next ws
    
End Sub

 

[tjamestx]

The following macro will loop through each worksheet within the workbook running this code, and clear the contents of any and all embedded Word documents...

Sub ClearContentsFromAllEmbeddedWordDocuments()

    Dim ws As Worksheet
    Dim objOLE As OLEObject
    Dim objWord As Object
   
    For Each ws In ThisWorkbook.Worksheets
        For Each objOLE In ws.OLEObjects
            If objOLE.OLEType = xlOLEEmbed Then
                If Left(objOLE.progID, 13) = "Word.Document" Then
                    objOLE.Verb xlVerbOpen
                    Set objWord = objOLE.Object
                    With objWord
                        .Application.Selection.WholeStory
                        .Application.Selection.Delete
                        .Parent.Quit
                    End With
                End If
            End If
        Next objOLE
    Next ws
   
End Sub

Domenic, All works perfectly and really I can't thank you enough. This will make life so much easier!

 

[Domenic]

That's great, glad I could help.

Cheers!