Word Hacks [Electronic resources]

Hack 31 Find/Replace in Multiple Files

Make the same substitution on several files at once using the code in this hack.

Find and Replace is a real time-saver, but when you have to perform the same substitution on multiple files, it can seem like more of a hindrance than a help.

If you regularly perform the same types of substitutions on multiple documents, developing a set of macros to do the work for you can represent a real improvement in efficiency.

4.6.1 The Code

For example, say your law firm, Dewey & Cheatham, just added a partner, and now you're Dewey, Cheatham & Howe. The following macro searches all the Word documents in the folder C:\My Documents and replaces the old name with the new name wherever it occurs:

Sub FindReplaceAllDocsInFolder( )
Dim i As Integer
Dim doc As Document
Dim rng As Range
With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents"
.SearchSubFolders = False
.FileType = msoFileTypeWordDocuments
If Not .Execute( ) = 0 Then
For i = 1 To .FoundFiles.Count
Set doc = Documents.Open(.FoundFiles(i))
Set rng = doc.Range
With rng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "Dewey & Cheatem"
.Replacement.Text = "Dewey, Cheatham & Howe"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With
doc.Save
doc.Close
Set rng = Nothing
Set doc = Nothing
Next i
Else
MsgBox "No files matched " & .FileName
End If
End With
End Sub

The macro uses the FileSearch object to examine each file in the folder. If it finds a Word document, it opens the file, changes the name wherever it occurs, and then saves and closes the file. If the macro finds no Word files in the folder, it displays a message on the screen.

Place this macro in the template of your choice [Hack #50] and either run it from the ToolsMacroMacros dialog or assign it a button on a menu or toolbar [Hack #1].

4.6.2 Hacking the Hack

The code in the first section has a subtle problem related to the Find object. When you perform a substitution from VBA, it includes only the main part of the document in the substitution. It leaves the headers, footers, comments, footnotes, text boxes, and so forth out of the search.

To modify the macro above to search every nook and cranny in a document, wrap the replacement inside a For Each loop [Hack #66] that cycles through each part of the document. The modified sections are highlighted in bold:

Sub FindReplaceAllDocsInFolder( )
Dim i As Integer
Dim doc As Document
Dim rng As Range
With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents"
.SearchSubFolders = False
.FileType = msoFileTypeWordDocuments
If Not .Execute( ) = 0 Then
For i = 1 To .FoundFiles.Count
Set doc = Documents.Open(.FoundFiles(i))
For Each rng In doc.StoryRanges
With rng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "Dewey & Cheatem"
.Replacement.Text = "Dewey, Cheatem & Howe"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute Replace:=wdReplaceAll
End With
Next rng
doc.Save
doc.Close
Set rng = Nothing
Set doc = Nothing
Next i
Else
MsgBox "No files matched " & .FileName
End If
End With
End Sub