Word - Execute lots of search and replaces efficiently?

[rgmatthes]

Hi all,

Back again with what is presumably an easy question, but I did some searches and found nothing.

I edit documentation. I have reason to execute dozens of search and replaces in nearly every doc I work on as a means to make corrections, so it makes sense to save time and write these searches into a macro.

The following is code that was generated when I recorded a search and replace action using the Record Macro feature built into Word. It obviously executes a single search and replace - "Word A" for "Word B".




Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "Word A"
.Replacement.Text = "Word B"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll


I know that to run several search and replaces one after another, all I'd need to do is essentially copy this code several times. But this is likely too much code – is there a more efficient way to execute many, many search and replaces? Maybe a way to store terms to a matrix or range and execute a search and replace based on that? Some of my search terms would require case sensitivity, and some would require finding whole word instances, but I think getting around this proublem would be as easy as grouping terms based on search requirements, right?

If there's an efficient way to go about this, I'd do hundreds, maybe even thousands of search and replaces. See, I created an AutoCorrect with over 12,000 corrections (Word ships with about 960), so I'd likely load up those entries into this macro. I suppose if you guys can help me, I'd host the file somewhere for you guys to download. If you're interested, of course. :)

So, any ideas? I'm running Windows 7 and Office 2010, btw, if it makes any difference.

Thanks in advance!! :thumb

[fumei]

Office 2010....shudder. I refuse to use even 2007, so I do not know if they have changed things enough that the following no longer applies.

Generally speaking, the answer to your question is yes.

First off, you are using Selection for your Find operation. Not a good idea. Use Range.

Second, yes, you can use an array of search words, and action them sequentially. Yes, you could read from a file. Attached is a very simple demo.

The document has three small Continuous sections to better demonstrate. Each section has different actions applied to it.

Section 1 is changed from:

The quick brown fox jumps over the lazy dog.

to

The quick yellow giraffe kissed wetly the snarling lion.

So "brown" is replaced with "yellow"; "fox" is replaced with "giraffe" etc.


Section 2 actions is simply changes "fox" to "giraffe" - still using the array - and applies Bold and Highlight to the changed word.

Section 3 changes some words into phrases, using a different set of arrays.


The quick brown fox jumps over the lazy dog. (9 words)

The snoozing parrot in the tree laughs at the scheming Ponzi fraudster. (12 words)

So:

"quick brown fox" turns into "snoozing parrot in the tree" (3 words into 4)
"jumps" turns into "laughs"
"over the lazy dog" turns into "at the scheming Ponzi fraudster" (4 words into 5)

Hopefully this can get you going to a solution. Click "Change Stuff" on the top toolbar. As you will see, by using Range (rather than Selection) you can perform a number of actions pretty fast. Unless you have a slow machine, it should just change in a blink of an eye.

Here is the code:
Option Explicit

Sub ChangeStuff_1()
Dim r As Range
Dim FirstWord() As String
Dim SecondWord() As String
Dim ThirdWord()
Dim FourthWord()
Dim j As Long

FirstWord = Split("brown fox jumps over lazy dog")
SecondWord = Split("yellow giraffe kissed wetly snarling lion")

ThirdWord = Array("quick brown fox", _
"jumps", _
"over the", _
"lazy dog")

FourthWord = Array("snoozing parrot in the tree", _
"laughs", _
"at the", _
"scheming Ponzi fraudster")

' Section 1 actions
For j = 0 To UBound(FirstWord())
Set r = ActiveDocument.Sections(1).Range
With r.Find
.ClearFormatting
.Text = FirstWord(j)
.Replacement.Text = SecondWord(j)
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
Next
j = 0
Set r = Nothing

' Section 2 actions
Set r = ActiveDocument.Sections(2).Range
With r.Find
.ClearFormatting
.Text = FirstWord(1)
With .Replacement
.Text = SecondWord(1)
.Highlight = True
.Font.Bold = True
End With
.Execute Replace:=wdReplaceAll
End With
j = 0

' Section 3 actions
For j = 0 To UBound(ThirdWord())
Set r = ActiveDocument.Sections(3).Range
With r.Find
.ClearFormatting
.Text = ThirdWord(j)
.Replacement.Text = FourthWord(j)
.MatchWholeWord = True
.Execute Replace:=wdReplaceAll
End With
Next
End Sub

[macropod]

Hi rgmatthes,

In addition to what fumei suggested, you may be able to dramatically cut down the number of Find/Replace operations by working with wildcards. In general terms, that applies where you have a set of strings with certain similarities to which a common change needs to be made. Without knowing the details of your "over 12,000 corrections" :bug:, it'd be hard to say whether this might be the case.

For an idea of the power of a wildcard approach, see my post at: http://www.vbaexpress.com/forum/showthread.php?p=197286#post197286. The macro is designed to work on the file in the link from the post I replied to.

[Paul_Hossler]

1. Wildcards ARE your best friend

2. I also have a 'Super Replace' macro, but instead of an array, I just have a series of Calls to a subroutine that has the S&R logic, with options passed (MatchCase, WholeWord, Wildcards). It does not do any format testing, etc. Just text replaces

3. There are 11 different story types in 2003, 17 in 2007, and it seems you need to go through all types, but then there's a 'gotcha' about using .NextStory to move to the next instance of that story type. MS Help has a good explaination. So the Sub is just a wrapper for the S&R logic, but goes through all Stories and all instances.


4. Sample of various calls --


Call StrReplace("cat", "dog")
Call StrReplace("bird", "chicken")
Call StrReplace("TOM", "Bill", True, True)

Application.StatusBar = "Replacing space+tab with single tab"
Call StrReplace(" {1,}^t", "^t", , , True)

Application.StatusBar = "Replacing tab+spaces with single tab"
Call StrReplace("^t {1,}", "^t", , , True)

Application.StatusBar = "Replacing tab+paragraph with single paragraph mark"
Call StrReplace("^t{1,}^13", "^p", , , True)

Application.StatusBar = "Replacing multiple spaces with single space"
Call StrReplace(" {2,}", " ", , , True)

Application.StatusBar = "Replacing paragraph+space with single paragraph mark"
Call StrReplace("^13 {1,}", "^p", , , True)

Application.StatusBar = "Replacing space+paragraph with single paragraph mark"
Call StrReplace(" {1,}^13", "^p", , , True)
Application.StatusBar = "Replacing multiple paragraph marks with single paragraph mark"
Call StrReplace("^13{2,}", "^p", , , True)




Sub StrReplace(OldStr As String, NewStr As String, _
Optional WholeWord = False, _
Optional MatchCase = False, _
Optional WildCard = False)


It's probalby brute force and not nearly as effeicant as it could be, but I was going for ease of maintainability

Paul

[Paul_Hossler]

Additional ref:

http://word.mvps.org/faqs/MacrosVBA/FindReplaceAllWithVBA.htm


http://gregmaxey.mvps.org/VBA_Find_And_Replace.htm


Paul

[fumei]

Go wildcards! Good posts guys.

Yeah....12,000 corrections....I am with macropod with the bugeyed. YIKES!

[rgmatthes]

Hi everyone!
Back at this today - thanks for all the great responses! It'll take me a while to look through all this info, but I'm sure I'll find the solution among your replies (maybe taking a little from each of you).

As promised, here's the AutoCorrect with over 12,000 entries. Keep in mind, this is no macro, but an AutoCorrect List file. It silently and swiftly runs in the background whenever an Office program is opened and replaces common typos, capitalization errors, and even some grammar errors with the proper corrections as you type them.

If for any reason you're unhappy with any of the corrections, you can delete the entries by going into Word's proofing options and checking out the AutoCorrect preferences (via a tab or button, depending on your version of Word). A long list of entries should appear. Just search for the false positive and delete it and you're good to go. You may need to do this once or twice, but it's well worth automatted corrections - instant fixes to 12,000 of the most common typos is nothing to sneeze at.

Anyway, to download, go here: [link removed, see below]. The download will likely expire in a few days, so act quick!

To use this file, all you have to do is replace your current ACL file with this one. To find your current ACL file, navigate to one of thes folders:

C:\Users\[account name]\AppData\Roaming\Microsoft\Office
C:\Documents and Settings\[account name]Application Data\Microsoft\Office

Depending on your version of Windows, it may be somewhere else, too. If neither of these folders exists, just do a search for "MSO1033.acl", back that guy up (just in case), and replace it with the version you're downloading. Works across all Office programs - Word, Excel, Outlook, Powerpoint, etc.

Thanks again for your responses, guys! :hi:

Edit: Well, I didn't want to have this file permanently available... hence Filedropper's expiring links... but it looks like FileDropper is acting up anyways. So maybe in a few days I'll come and remove this zip. Oh well, enjoy!

[lucas]

You can attach your document to a post here....click on go advanced and scroll down. Look for the button that says "manage attachments"