Replace text in Word document from Excel

Question

I need to go through a row in excel table and use cell values to do replacements in Word document. I used record macro to get the code, it actually replaces the text. But when I use it from Excel Macros it doesn't work.

Dim WordApp As Object
Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True

Dim WordDoc As Object
Set WordDoc = WordApp.Documents.Open(doc_path_str)

Dim find_what, find_repl As String

For col_idx = params_hdr_range.Column To params_hdr_range.Column + params_hdr_range.Columns.Count - 1
    find_what = CStr(scnd_sheet.Cells(params_hdr_range.Row - 1, col_idx).Value)
    find_repl = CStr(scnd_sheet.Cells(model_found_range.Row, col_idx).Value)
    WordApp.Selection.Find.ClearHitHighlight
    WordApp.Selection.Find.ClearFormatting
    WordApp.Selection.Find.Replacement.ClearFormatting
    With WordApp.Selection.Find
      .Text = find_what
      .Replacement.Text = find_repl
      .Forward = True
      .Wrap = wdFindContinue
      .Format = False
      .MatchCase = False
      .MatchWholeWord = False
      .MatchWildcards = False
      .MatchSoundsLike = False
      .MatchAllWordForms = False
    End With
    WordApp.Selection.Find.Execute Replace:=wdReplaceAll
Next col_idx

find_what and find_repl have proper values ("{MODEL}" and "F22-2"), the same ones I used when recorded the macro, but no replacements are made. The only thing this code does it selects the text "{MODEL}" in the document. But it doesn't replace it, and though it goes through a lot of columns and other values (e.g. "{PRICE}"), nothing else happens.
How can I fix this?

Did you add a reference to Word in your Excel VBA project? If you didn't, Excel isn't going to know the values of Word constants such as wdReplaceAll — Tim Williams

Tim Williams Tim Williams · Accepted Answer · 2014-03-27T14:51:52

Unless you add a reference to the Word object library in your Excel VBA project, Excel isn't going to know the values of Word constants such as wdReplaceAll.

You can either add the reference, declare the constants in your Excel VBA, or use the constants' values instead (which can be found in the Word VBA Object Browser)

Replace text in Word document from Excel

1 Answers