Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Finding and Replacing Text in a Microsoft Word Document

Change is inevitable, but that doesn’t mean it’s always a barrel of laughs. For example, suppose you work for Contoso – hey, that reminds us: Do you know a guy named Ken Myer who used to work at Contoso? Tall, kind of reddish-hair?

Well, anyway, you work at Contoso and you come in one morning only to find that the company has changed its named to Fabrikam. (Hey, what company wouldn’t want to change its name to something really cool like Fabrikam?) That’s fine, except for one problem: you have a bunch of Word documents – templates, training materials, form letters – that all use the old company name: Contoso. Now you have to go through each of those documents and replace every instance of Contoso with Fabrikam. Oh, good….

Admittedly, finding and replacing text in Microsoft Word isn’t too terribly hard; most of us are very familiar with the Find and Replace dialog box:

The only problem is that you have to open each and every Word document, manually bring up the Find and Replace dialog box, type in the appropriate criteria, and then click Replace All. If you have 2 or 3 documents that’s not too bad; the story’s a bit different, however, if you have 200 or 300 documents. A job like that could take forever. Say, what do you suppose the odds are that there’s a way to programmatically find and replace text in a Word document?

Well, thanks to a major miscalculation at the Kentucky Derby (no one told us that Secretariat was dead, let alone that he wasn’t even entered in this year’s race) the Scripting Guys are now opposed to gambling and all other vices; therefore we don’t know what the odds are. But we can tell you that it’s very easy to programmatically find and replace text within a Word document. For example, here’s a simple little script that lets us know whether the word Contoso can be found anywhere in the document C:\Scripts\Test.doc:

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

Set objDoc = objWord.Documents.Open("C:\Scripts\Test.doc")
Set objSelection = objWord.Selection

objSelection.Find.Text = "Contoso"
objSelection.Find.Forward = TRUE
objSelection.Find.MatchWholeWord = TRUE

If objSelection.Find.Execute Then
    Wscript.Echo "The search text was found."
Else
    Wscript.Echo "The search text was not found."
End If

So what did we do here? Well, we began by creating an instance of Microsoft Word (the Word.Application object) and then setting the Visible property to True; we did the latter just so we can see the document onscreen. Next we use the Open method to open the document C:\Scripts\Test.doc; with the document open we then create an instance of the Selection object right at the very start of the document.

Now comes the fun part. It turns out the Selection object has a child object: Find. As the name implies, the Find object is used to find things in a document. Today we’re going to focus on finding text; however, the Find object can also be used to locate formats, styles, pictures, special characters, and anything else you can locate using the Find and Replace dialog box. We’ll deal with that fancy stuff some other time; for now, we’ll just see if we can locate the word Contoso.

We configure three properties of the Find object:

• Text. This is simply the text that we want to search for; thus we set the value of the Text property to Contoso.

• Forward. This is the direction we want to search. Because we created the Selection object right at the beginning of the document we want to go forward (that is, towards the end of the document). Hence we set the value of the Forward property to True.

• MatchWholeWord. This simply instructs the script to match only the word Contoso. In this case we’re unlikely to find the word Contoso embedded in any other word. However, suppose we wanted to replace the word cat with the word dog. If whole word matching was off, we’d replace any instances of the letters c-a-t with dog; in other words, a word like catalog would end up being changed to dogalog. To prevent any weirdness like that, we set MatchWholeWord to True.

Note. These are far from the only properties than can be configured for the Find object. For a complete list, take a look at the Microsoft Word VBA Language Reference on MSDN.

After we’ve configured the properties of the Find object we then call the Execute method to search the document. Execute returns True if the search text was found and False if the text can’t be found; we take a look at the value returned by the Execute method and then report back the appropriate message. That’s what we do here:

If objSelection.Find.Execute Then
    Wscript.Echo "The search text was found."
Else
    Wscript.Echo "The search text was not found."
End If

Now that’s all well and good: this script lets us know if the word Contoso can be found anywhere in Test.doc. But, to be honest, that information doesn’t matter much: if the document doesn’t contain the word Contoso we don’t really care, and if it does contain the word Contoso then we want that replaced with the word Fabrikam. So how do we do the replacing part of a find and replace script?

Actually we do it just like this:

Const wdReplaceAll  = 2

Set objWord = CreateObject("Word.Application")
objWord.Visible = True

Set objDoc = objWord.Documents.Open("C:\Scripts\Test.doc")
Set objSelection = objWord.Selection

objSelection.Find.Text = "Contoso"
objSelection.Find.Forward = TRUE
objSelection.Find.MatchWholeWord = TRUE

objSelection.Find.Replacement.Text = "Fabrikam"
objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll

As you can see, much of this script is very similar to the first script we showed you. We do have a new first line: we start off by defining a constant named wdReplaceAll and setting the value to 2; we’ll use this constant later on to tell the script to automatically replace all instances of the word Contoso. We then open our Word document and configure the properties of the Find object, using the exact same code we used in the script that simply reported whether or not the word Contoso could be found in the document.

It’s really only here that our scripts differ. Because we want to actually replace existing instances of Contoso we need to set some properties of the Replacement object (a child object of the Find object). Because our sole concern is a straightforward replacement of one word with another the only property we need to configure is the Text property, which is simply our replacement text. We want to use Fabrikam as our replacement text so the code that configures the Text property looks like this:

objSelection.Find.Replacement.Text = "Fabrikam"

We then call the Execute method, although we have to confess that this time the code for doing so looks a bit quirky:

objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll

What’s the deal with all the commas? Well, the Execute method has a bunch of optional parameters (for a complete list, see the Word VBA Language Reference). In our first script – the one that just searched for the text – we didn’t need any of those optional parameters; thus we called the Execute method without any parameters. To replace text, however, we need to indicate that we want to automatically replace all instances of the word Contoso; that’s why we see the constant wdReplaceAll in the parameter list.

But what about all the commas? Well, to indicate that we want to replace all instances of the word Contoso our constant wdReplaceAll needs to be the eleventh item in the parameter list; that means it must be prefaced by 10 other parameters. We don’t want to configure any of those parameters, but we still have to indicate their presence. Thus we use blank parameters, separated by commas. It looks crazy, but it works: run the script and every instance of Contoso will be replaced with the word Fabrikam.

We still don’t know whether change is good or not, but as long as it’s this easy to make those changes, who cares? And because scripts do make it so quick and easy to replace text in Word documents that leaves us plenty of time to pursue other activities. (Don’t spread this around, but we have a hot tip on Man o’ War for the upcoming Preakness.)