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?
CodePudding user response:
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.