VBA Find and Replace in word based on Database in

Question

I am looking for a way to Find and Replace text in a Word Document based on Data in Excel in excel

More specifically:

Column A = Text I want to find Column B = Text I want to replace the found text with

I already know basic Find and Replace code in VBA, but I'm struggling to work out how to do it like this (Cross-application).

Can anyone help me?

Thanks :)

REQUESTED SCREENSHOT:

I've clipped a sample of my spreadsheet due to potentially sensitive information being displayed but this gives the jist.

Answer 1

The following code needs the reference to the MS Word 14.0 Object Library or whatever is the equivalent on your end. If you don't want early binding, it's up to you to convert it to late binding.

Sub FindReplaceInWord()

    Dim Wbk As Workbook: Set Wbk = ThisWorkbook
    Dim Wrd As New Word.Application
    Dim Dict As Object
    Dim RefList As Range, RefElem As Range

    Wrd.Visible = True
    Dim WDoc As Document
    Set WDoc = Wrd.Documents.Open("C:\xxx\Doc1.docx") 'Modify as necessary.

    Set Dict = CreateObject("Scripting.Dictionary")
    Set RefList = Wbk.Sheets("Sheet1").Range("A1:A3") 'Modify as necessary.

    With Dict
        For Each RefElem In RefList
            If Not .Exists(RefElem) And Not IsEmpty(RefElem) Then
                .Add RefElem.Value, RefElem.Offset(0, 1).Value
            End If
        Next RefElem
    End With

    For Each Key In Dict
        With WDoc.Content.Find
            .Execute FindText:=Key, ReplaceWith:=Dict(Key)
        End With
    Next Key

    'Enable the following three if you want.
    'WDoc.Save
    'WDoc.Close
    'Wrd.Quit

End Sub

Screenshots:

Word Document (before running code)

Reference List in Excel

Word Document (after running code)

Let us know if this helps.

EDIT:

For reference, the code doesn't have problems with your list. I think it's something else altogether. See my screenshot.

As you can see, using Debug.Print Key, Dict(Key) shows it's safe. I have modified the code above to change something that might be causing this. :)