Word: Macro to run multiple wildcard find and replace routines

After some digging around, I’ve found a way to save myself hours of running separate find and replace routines using wildcards in Microsoft Word.

The routines I commonly use already save me time, but now I’ve got one macro that runs them all at once — currently 110 of them that are just for replacing an ordinary space with a non-breaking space!

I didn’t write the macro — that honor goes to Microsoft Word MVPs Graham Mayor who wrote the initial macro, and Doug Robbins who helped me tweak it to do what I wanted it to do. Details are here: http://answers.microsoft.com/en-us/office/forum/office_2003-word/macro-for-performing-more-than-one-find-and/bd931bf7-5ebe-4650-924c-d15c9512129c?page=1

NOTE: EditTools software (http://wordsnsync.com/edittools.php) has a neat interface for setting up your wildcard find and replace routines, along with a facility to set up a script to run them all, and I use that on my own PC. However, I’m not allowed to install that software on my work PC (long story…), so I wanted to figure out a way to emulate what an EditTools script does by creating a macro to achieve the same end.

UPDATE: PerfectIt software (v3 onwards; http://intelligentediting.com) can be customized to include wildcard find/replace routines too, without you having to know anything about macros.

Before doing these steps, you need to be familiar with:

• accessing the VBA area of Word
• using wildcards to find and replace in Word.

I won’t cover either of those things in this post.

Step 1: Create a table of your wildcard find/replace routines

The first stage in getting this to work is to create a 2-column table in Word, listing what you want to find in the left column and what you want to replace it with in the right column. Use one row for each find/replace routine. Use the same syntax as you would for a wildcard find/replace.

This table will take a while to set up. You should test each wildcard find/replace routine on a document BEFORE you add it to the list, just to make sure it works correctly and doesn’t mess up anything else. These find/replace routines will all be done as ‘replace all’, so you will NOT have the opportunity to accept some and reject others. Testing is crucial to make sure you don’t replace something you shouldn’t.

In the example below, I’ve set up routines for replacing a standard space between a number and a month of the year with a non-breaking space (^s) between those two elements, as well as replacing a standard space between a number and a unit of measure (e.g. lux, nm) or a word that often follows a number in the documents I work on (e.g. fish, indiv [for ‘individual’ or ‘individuals’). As I said, I have some 110 of these…

Once you’ve created your table, save it to a location that your macro can pick it up from easily — I suggest somewhere on your local drive, not a network location (unless you intend sharing it with others). I put mine in my Word > STARTUP folder, which is where I keep my macros.dotm file.

Step 2: Set up the macro to work with the table

Add the macro below to an existing document, or better, an existing template, or even better, a central macros template that loads whenever you open Word.

Once you’ve added it, change the line that starts with sFname and has the file path — that path points to MY file on MY computer. You need to change it to YOUR file name and YOUR file path.

NOTE: Copy all the code below to the clipboard — it goes off the page, so don’t type it out as you’ll miss some of it or could make a typo.

Sub ReplaceFromTableList()
' from Doug Robbins, Word MVP, Microsoft forums, Feb 2015, based on another macro written by Graham Mayor, Aug 2010
 Dim oChanges As Document, oDoc As Document
 Dim oTable As Table
 Dim oRng As Range
 Dim rFindText As Range, rReplacement As Range
 Dim i As Long
 Dim sFname As String
 'Change the path in the line below to reflect the name and path of the table document
 sFname = "C:\Users\rhonda\AppData\Roaming\Microsoft\Word\STARTUP\find_and_replace_routines_macro.docx"
 Set oDoc = ActiveDocument
 Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)
 Set oTable = oChanges.Tables(1)
 For i = 1 To oTable.Rows.Count
     Set oRng = oDoc.Range
     Set rFindText = oTable.Cell(i, 1).Range
     rFindText.End = rFindText.End - 1
     Set rReplacement = oTable.Cell(i, 2).Range
     rReplacement.End = rReplacement.End - 1
     Selection.HomeKey wdStory
     With oRng.Find
             .ClearFormatting
             .Replacement.ClearFormatting
             .MatchWildcards = True
             .Text = rFindText.Text
             .Replacement.Text = rReplacement.Text
             .Forward = True
             .Wrap = wdFindContinue
             .Execute Replace:=wdReplaceAll
       End With
 Next i
 oChanges.Close wdDoNotSaveChanges
End Sub

Step 3: Test that it works

After setting up the macro, run it on a test document — copy an existing document and test on the copy to make sure you don’t mess up anything.

As my file had 110 find/replace routines for changing some normal spaces to non-breaking spaces, I needed to test it on a document that had many normal spaces for the elements I wanted to change to non-breaking spaces. I opened a copy of an existing 400p Word document, used Word’s normal find/replace to replace all existing non-breaking spaces with a normal space (some 3000 of them in that document I tested it on!). Then I ran the ReplaceFromTableList macro to see if it worked.

It did. Beautifully. And it took about 3 minutes to run ALL those routines on that 400p document. The end result was a document that had more than 5000 non-breaking spaces added to it (I checked the number by taking them all out again and replacing them with normal spaces!).

One 400p Word document, 110 separate find/replace routines. In 3 minutes. In one macro.

That’s gotta be a win for automating tedious, routine tasks! Now, to think about what other find/replace routines I can add to the table…

[Links last checked March 2015]