VBA search replace text in word document from excel

Question

I am trying to make an excel document that I can paste a list of words to search and replace into. I want to use the excel document to search and replace these words in a word document.

I am having issues getting the code to function.

Sub SearchReplace()

Dim WordDoc As Object, N As Variant, i As Integer, j As Integer
i = Range("C2").Value  'pulls length of list from an excel function located in cell C2
N = Range("B4:C" & CStr(i + 3)).Value
Set WordDoc = CreateObject(Class:="Word.Application")
    WordDoc.Visible = True
WordDoc.Documents.Open Filename:="C:\WordTest.docm"
WordDoc.Activate
With WordDoc.ActiveDocument
    For j = 1 To i
    With .Range
        With .Find
            .ClearFormatting
            .Replacement.ClearFormatting
            .MatchWildcards = False
            .Wrap = wdFindContinue
            .Text = N(1, j)
            .Replacement.Text = N(2, j)
            .Execute
         End With
    End With
    Next j
End With
WordDoc.Quit
Set WordDoc = Nothing
End Sub

I corrected the code based on the recommendations, and I no longer get any errors. However the code does not appear to find and replace anything in the specified word document. I tried to simplify the code to check the find & replace portion by typing in a specific word "text" and a specific word "replace" to find and replace in a word document, which contains the word "text". This still result in no change. I removed the line that closed the document in case the issue was the document was not being saved after being updated, but that also was not successful, so I added WordDoc.Quit back into the code.

Sub SearchReplace()

Dim WordDoc As Object, N As Variant, i As Integer, j As Integer

i = Range("C2").Value  'pulls length of list from an excel function 
located in cell C2
N = Range("B4:C" & CStr(i + 3)).Value
Set WordDoc = CreateObject(Class:="Word.Application")
WordDoc.Visible = True
WordDoc.Documents.Open Filename:="C:\WordTest.docm"
WordDoc.Documents("WordTest.docm").Activate
With WordDoc.ActiveDocument
For j = 1 To i
With .Range
    With .Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .MatchWildcards = False
        .Wrap = 1
        .Text = "text" 'N(j, 1)
        .Replacement.Text = "replace" 'N(j, 2)
        .Execute 2
     End With
End With
Next j
End With
WordDoc.Quit
Set WordDoc = Nothing
End Sub

Answer 1

I was able to get the code to function. The code searches a specified Microsoft Word Document from Excel using a list of find replace strings from the excel sheet (B4:B5004 (find), C4:C5005 (replace)) stored in an array in the code.

To get the code to function I had to include the Microsoft Word 16 Object Library.

From the excel VBA editor window. Tools > References then ensure Microsoft Word 16.0 Object Library is checked.

Option Explicit

Sub SearchReplace()

   Dim WordApp As Object, WordDoc As Object, N As Variant, i As Integer, j As Integer
   i = Range("C2").Value  'pulls length of list from an excel function located in cell C2 =COUNTIF(B4:B5005,"*")
   N = Range("B4:C" & CStr(i + 3)).Value
   Set WordApp = CreateObject(Class:="Word.Application")
   Set WordDoc = WordApp.Documents.Open("C:\WordTest.docm")    
   WordDoc.Visible = True
   For j = 1 To i
        With WordApp
           With WordDoc.Content.Find
                .Text = N(j, 1)
                .Replacement.Text = N(j, 2)
                .Wrap = wdFindContinue
                .MatchWholeWord = True
                .Execute Replace:=wdReplaceAll
          End With
        End With
    Next j
    WordApp.ActiveDocument.Save
    WordApp.ActiveDocument.Close
    WordApp.Quit

    Set WordApp = Nothing
    Set WordDoc = Nothing
End Sub

Answer 2

The "Compile error: Variable not defined" problem is due to your use of late binding with a named Word constant. Either change

.Wrap = wdFindContinue

to:

.Wrap =  1

or insert:

Const wdFindContinue as Long = 1

at the top of your code.

In any event, as it stands, your existing code won't replace anything. Instead of:

.Execute

you need either:

.Execute 2

or:

.Execute wdReplaceAll

(to replace all instances) or:

.Execute 1

or:

.Execute wdReplaceOne

(to replace only the first instance)

As with wdFindContinue, you'd need to supplement wdReplaceAll or wdReplaceOne with:

Const wdReplaceAll as Long = 2

or:

Const wdReplaceOne as Long = 1

as appropriate.