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

tjamestx

New Member
Joined
Mar 21, 2014
Messages
9
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.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,756
You can call the following procedure from the BeforeClose event handler. Change the references to the worksheet and the embedded object accordingly.

VBA Code:
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

New Member
Joined
Mar 21, 2014
Messages
9
Domenic, Thank you for the reply. I several meetings this morning but, I will try this out and reply back on the outcome...
 

tjamestx

New Member
Joined
Mar 21, 2014
Messages
9
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,756

ADVERTISEMENT

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...

VBA Code:
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
 
Solution

tjamestx

New Member
Joined
Mar 21, 2014
Messages
9
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...

VBA Code:
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!
 

Forum statistics

Threads
1,144,339
Messages
5,723,800
Members
422,517
Latest member
VisioExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top