CODEWITHSUNDEEP
excelvbams-wordole
Tuberose
Tuberose

Reputation: 444

Error when using find/replace method in an embedded Word document in Excel VBA

I have an embedded word document in my worksheet, names "Rec1"

So using before:

Sub ReplaceTextinOLEObject
    Dim oDoc As OLEObject
    Set oDoc = Worksheets("Sheet1").OLEObjects("Rec1")
    oDoc.Activate
    With oDoc.Content.Find
        .ClearFormatting
        .Text = "hi"
        .Replacement.ClearFormatting
        .Replacement.Text = "hello"
        .Execute Replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue
    End With
    Word.Application.Quit wdDoNotSaveChanges
End Sub

When perform running above, the With part committed below error:

Run-time error '438':
object doesn't support this property or method

What is the problem of the using Find object for OLE embedded word documents?

Upvotes: 1

Views: 445

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

The issue here is that the OLEObjects has no .Content property. That is also what the error tells you.

Therefore you have to use oDoc.Object.Content.Find instead. And you don't need to oDoc.Activate the document, it will also work without.

The following should work:

Public Sub ReplaceTextinOLEObject
    Dim oDoc As OLEObject
    Set oDoc = Worksheets("Sheet1").OLEObjects("Rec1")
    'oDoc.Activate 'remove this line, activate is not needed here
    With oDoc.Object.Content.Find
        .ClearFormatting
        .Text = "hi"
        .Replacement.ClearFormatting
        .Replacement.Text = "hello"
        .Execute Replace:=wdReplaceAll, Forward:=True, Wrap:=wdFindContinue
    End With
    Word.Application.Quit wdDoNotSaveChanges
End Sub

Note: Of course the "Microsoft Word 16.0 Object Library" (version may vary) needs to be referenced to run this code.

Upvotes: 1

Related Questions