How can I find and replace text in Word using Excel VBA?

Question

I am using Excel VBA to open a document in Word. Once the document is open the goal is to search for "InsuranceCompanyName" and replace it with the company's name.

I have tried

wordDoc.Find.Execute FindText:="InsuranceCompanyName", ReplaceWith:="Fake Ins Co"

and

wordDoc.Replace What:="InsuranceCompanyName", Replacement:="Fake Ins Co"

and also

For Each myStoryRange In ActiveDocument.StoryRanges

    With myStoryRange.Find
        .Text = "InsuranceCompanyName"
        .Replacement.Text = "Fake Ins Co"
        .WrapText = wdFindContinue
        .Execute Replace:=wdReplaceAll
    End With 
Next myStoryRange

The full code is listed below.

Sub FindReplace()

Dim wordApp As Object 
Dim wordDoc As Object 
Dim myStoryRange As Range

'sets up the word app
Set wordApp = CreateObject("Word.Application")
wordApp.Visible = True 

'opens the document that we need to search through 
Set wordDoc = wordDoc = wordApp.Documents.Open("C:\Users\cd\LEQdoc.docx")

'here is where the find and replace code would go

End Sub

For the first method I get the error:

Object doesn't support this property or method.

For the second: the same error

The third method:

argument not optional

in regards to the .Find in

With myStoryRange.Find

Set wordDoc = wordDoc = wordApp.Documents.Open("C:\Users\cd\LEQdoc.docx") should be Set wordDoc = wordApp.Documents.Open("C:\Users\cd\LEQdoc.docx") — Tim Williams
– Tim Williams, Commented Aug 23, 2019 at 17:55
ActiveDocument is not an Excel concept, so you need to preface that with wordApp i.e. wordApp.ActiveDocument — Tim Williams
– Tim Williams, Commented Aug 23, 2019 at 17:56
Also ensure that you declare the constants wdFindContinue, wdReplaceAll etc since you are late binding with MS Word — Siddharth Rout
– Siddharth Rout, Commented Aug 23, 2019 at 18:00
Change Dim myStoryRange As Range to Dim myStoryRange As Object — Siddharth Rout
– Siddharth Rout, Commented Aug 23, 2019 at 18:07

Siddharth Rout · Accepted Answer · 2019-08-23 18:18:59Z

4

Try this code

Option Explicit

Const wdReplaceAll = 2

Sub FindReplace()
    Dim wordApp As Object
    Dim wordDoc As Object
    Dim myStoryRange As Object

    '~~> Sets up the word app
    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = True

    '~~> Opens the document that we need to search through
    Set wordDoc = wordApp.Documents.Open("C:\Users\routs\Desktop\Sample.docx")

    For Each myStoryRange In wordDoc.StoryRanges
        With myStoryRange.Find
            .Text = "InsuranceCompanyName"
            .Replacement.Text = "Fake Ins Co"
            .Execute Replace:=wdReplaceAll
        End With
    Next myStoryRange
End Sub

In Action

edited Aug 23, 2019 at 18:18

answered Aug 23, 2019 at 18:08

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Cassandra Durell Over a year ago

This is great! It no longer gets hung up on that line and continues past it. However, it doesn't actually replace the text that it was supposed to find and replace.

Siddharth Rout Over a year ago

I have added the screenshot. You may have to refresh th epage to see it

Siddharth Rout Over a year ago

BTW I hope you declared Const wdReplaceAll = 2 on the top of your code ;)?

Cassandra Durell Over a year ago

Works perfectly! Thanks for the help. Always much appreciated.

Collectives™ on Stack Overflow

How can I find and replace text in Word using Excel VBA?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related