Iterating through excel document to replace words in word

Question

I have an excel document with two columns and 100 rows. One column is a word to look for in a word document and the second column is the word that I want to replace the searched word with.

I want to iterate through each row in the excel table and use the inputs to search and replace in a word document.

I have experience with VBA in Access but I have never used it with Word or Excel before and I am very confused.

The function I think will work is this:

function replace(find,replace)
with activedocument.content.find
.forward=True
.wrap=wdFindContinue
.Execute fintext:=find,replacewith:=replace,matchcase:=False,matchwholeword:=True
end with
end function

In access you would then call that function in an event, but word doesn't have events. I just want to run the code. When I select run a form asking me to create a macro appears and I get lost.

How do I run vba code in Word?

Thanks

paul bica paul bica · Accepted Answer · 2015-07-15T22:03:22

To run VBA code in Word press Alt-F8

It will display a form with all available Public Subs or Functions in the document.

For example:

Open a Word file and the VBA editor window (Alt-F11)
Click menu item: Insert -> Module
Create a myFindAndReplace Sub similar to this:

Option Explicit

Public Sub myFindAndReplace()

    Const MAX_ITEMS As Long = 3
    Dim i As Long, arr1(1 To MAX_ITEMS) As String, arr2(1 To MAX_ITEMS) As String

    arr1(1) = "find1"
    arr1(2) = "find2"
    arr1(3) = "find3"

    arr2(1) = "replace1"
    arr2(2) = "replace2"
    arr2(3) = "replace3"

    With Application.ActiveDocument.Content.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        For i = 1 To MAX_ITEMS
            .Text = arr1(i)
            .Replacement.Text = arr2(i)
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False
            .Execute Replace:=wdReplaceAll
        Next
    End With
End Sub

Back in Word click Alt-F8 and you'll get the available macros:

available macros form

Word has the following events in VBA module ThisDocument (under the Project with your file name):

Document_BuildingBlockInsert()
Document_Close()
Document_ContentControlAfterAdd()
Document_ContentControlBeforeContentUpdate()
Document_ContentControlBeforeDelete()
Document_ContentControlBeforeStoreUpdate()
Document_ContentControlOnEnter()
Document_ContentControlOnExit()
Document_New()
Document_Open()
Document_Sync()
Document_XMLAfterInsert()
Document_XMLBeforeDelete()

Now you can either

Build the entire two arrays (in Word) and use them directly in the code, as in the sample Sub
Or open the Excel file and iterate through each row to perform the replacement in Word

Option 2 is more convoluted

Iterating through excel document to replace words in word

1 Answers