Find and replace in Word using Excel VBA doesn't replace

Question

I'm using an Excel VBA macro to replace words in a template document with text found in the Excel spreadsheet. The format is trivial; in Excel column A has the search term and column B the replacement text, and in Word, the document has {searchterm} placeholders. I loop over the Excel sheet, look for rows with valid data in A and B and find replace. Complete code:

On Error Resume Next
Set wApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
     Set wApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Temp = Application.ActiveWorkbook.Path
Set wDoc = wApp.Documents.Open(Temp & "\Type some stuff.docx")
If wDoc Is Nothing Then
    MsgBox "Could not find the template file at: " & Temp & "\Type some stuff.docx"
    Exit Sub
End If
On Error Resume Next
wDoc.SaveAs Temp & "\Replaced some stuff.docx"
On Error GoTo 0
If wDoc Is Nothing Then
    MsgBox "Could not save the word file at: " & Temp & "\Replaced some stuff.docx"
    Exit Sub
End If

For R = 1 To LastR
    ST = WS.Cells(R, 1)
    RT = WS.Cells(R, 2)
    'and if they are OK, use find/replace
    If Len(ST) > 1 And Len(RT) > 1 Then
        wDoc.Content.Find.Execute Wrap:=wdFindContinue, FindText:="{" & ST & "}", ReplaceWith:=RT, Replace:=wdReplaceAll
        didSome = wDoc.Content.Find.found
    End If
Next R
wDoc.Save
wDoc.Close
Set wApp = Nothing

All values are valid: wDoc is a word document containing "This is something to change. {item1} Did this work.", ST is "item1", and RT is "replacing item 1". LastR is 3. The code runs through all the lines - it doesn't exit early or anything. This creates the file "New document.docx" as expected, but the text has not been replaced. Checking didSome always returns False.

I took the example from MS's Find.Execute page, changing only the Wrap. What have I missed?

freeflow · Accepted Answer · 2022-03-24 14:11:33Z

1

You have made a classic mistake of referring to a word enumeration 'wdFindContinue' when defining the word object as a late bound object (created using CreateObject). This means that wdFindContinue' will have the value 0 instead of the value 1 as defined in the wdFindWrap enumeration.

Thus you either need to replace wdFindContinue with the literal '1' or define wdFindContinue as a constant with a value of 1, or change to using an early bound reference for the Word object by adding a reference to the Word Object from Tools.References.

answered Mar 24, 2022 at 14:11

freeflow

4,3753 gold badges13 silver badges19 bronze badges

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

1 Comment

Maury Markowitz Over a year ago

Ugh... VBA at its finest. I will use a literal 1 and leave a note behind.

Collectives™ on Stack Overflow

Find and replace in Word using Excel VBA doesn't replace

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related