Batch Find-and-Replace Utility using a data file for inputs

[beerymr]

Using Word 2003...

I would like to use a data file (with a list of [text to find], [text to
replace]) to automate batch processing of find-and-replace within a single
document. My objective is to enforce an approved spelling/word usage list
that includes treatment of two-word sets that cannot be addressed through
either the dictionary or exclude dictionaries.


Examples: (due to the multiple word nature of this data, I expect
delimiting of fields will be required)



Find: Replace:

base plate baseplate

ballbearing ball bearing

damp proof dampproof

stormwater storm water

waste water wastewater

shutdown shutdown ***confirm use as noun/adjective or
use two words***

shut down shut down ***confirm use as verb or use one
word***





Ideally, the solution would be included in a global template that could be
readily distributed, together with an evolving data file. This needs to be
easily deployed to personnel in disparate locations.



Forgive me if I’ve not found a solution that has been offered to this
problem. I have only seen discussion of batch find-and-replace across
multiple files not multiple, varied replacements in the same file.

 

[Greg Maxey]

Take a look at:

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

Using Word 2003...

I would like to use a data file (with a list of [text to find], [text
to replace]) to automate batch processing of find-and-replace within
a single document. My objective is to enforce an approved
spelling/word usage list that includes treatment of two-word sets
that cannot be addressed through either the dictionary or exclude
dictionaries.


Examples: (due to the multiple word nature of this data, I expect
delimiting of fields will be required)



Find: Replace:

base plate baseplate

ballbearing ball bearing

damp proof dampproof

stormwater storm water

waste water wastewater

shutdown shutdown ***confirm use as
noun/adjective or use two words***

shut down shut down ***confirm use as verb or use
one word***





Ideally, the solution would be included in a global template that
could be readily distributed, together with an evolving data file.
This needs to be easily deployed to personnel in disparate locations.



Forgive me if I've not found a solution that has been offered to this
problem. I have only seen discussion of batch find-and-replace across
multiple files not multiple, varied replacements in the same file.

 

[Graham Mayor]

It has been a while since we looked at this add-in, so I should warn the OP
that the default settings for the files used are now directed to an E:
drive, which for many users will not exist? Using the add-in as supplied
will produce for most users an error message.

You need to change the lines
Private Const MyPreDefinedList$ = "E:\Find and Replace List.doc"
Private Const MyQuickList$ = "E:\MyQuickList.doc"

to
Private Const MyPreDefinedList$ = "C:\Find and Replace List.doc"
Private Const MyQuickList$ = "C:\MyQuickList.doc"

or some other suitable *and available* location - such as the user's My
Documents folder

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Take a look at:

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

Using Word 2003...

I would like to use a data file (with a list of [text to find], [text
to replace]) to automate batch processing of find-and-replace within
a single document. My objective is to enforce an approved
spelling/word usage list that includes treatment of two-word sets
that cannot be addressed through either the dictionary or exclude
dictionaries.


Examples: (due to the multiple word nature of this data, I expect
delimiting of fields will be required)



Find: Replace:

base plate baseplate

ballbearing ball bearing

damp proof dampproof

stormwater storm water

waste water wastewater

shutdown shutdown ***confirm use as
noun/adjective or use two words***

shut down shut down ***confirm use as verb or use
one word***





Ideally, the solution would be included in a global template that
could be readily distributed, together with an evolving data file.
This needs to be easily deployed to personnel in disparate locations.



Forgive me if I've not found a solution that has been offered to this
problem. I have only seen discussion of batch find-and-replace
across multiple files not multiple, varied replacements in the same
file.

 

[Greg Maxey]

Graham,

It has been a while since we have looked at it is right. I just posted an
updated ZIP file that lets the user pick his or her pre-defined list. The
Private Const MyPreDefinedList$ = "E:\Find and Replace List.doc is no longer
used in the code. Private Const MyQuickList$ = "E:\MyQuickList.doc should
has you say be changed to some other suitable and available path.

--
Greg Maxey - Word MVP

My web site http://gregmaxey.mvps.org

It has been a while since we looked at this add-in, so I should warn the
OP that the default settings for the files used are now directed to an E:
drive, which for many users will not exist? Using the add-in as supplied
will produce for most users an error message.

You need to change the lines
Private Const MyPreDefinedList$ = "E:\Find and Replace List.doc"
Private Const MyQuickList$ = "E:\MyQuickList.doc"

to
Private Const MyPreDefinedList$ = "C:\Find and Replace List.doc"
Private Const MyQuickList$ = "C:\MyQuickList.doc"

or some other suitable *and available* location - such as the user's My
Documents folder

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

Take a look at:

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

Using Word 2003...

I would like to use a data file (with a list of [text to find], [text
to replace]) to automate batch processing of find-and-replace within
a single document. My objective is to enforce an approved
spelling/word usage list that includes treatment of two-word sets
that cannot be addressed through either the dictionary or exclude
dictionaries.


Examples: (due to the multiple word nature of this data, I expect
delimiting of fields will be required)



Find: Replace:

base plate baseplate

ballbearing ball bearing

damp proof dampproof

stormwater storm water

waste water wastewater

shutdown shutdown ***confirm use as
noun/adjective or use two words***

shut down shut down ***confirm use as verb or use
one word***





Ideally, the solution would be included in a global template that
could be readily distributed, together with an evolving data file.
This needs to be easily deployed to personnel in disparate locations.



Forgive me if I've not found a solution that has been offered to this
problem. I have only seen discussion of batch find-and-replace
across multiple files not multiple, varied replacements in the same
file.

 

[Peter Jamieson]

Have you considered using Environ("TEMP") to get the name of the user's
TEMP folder and put the file there? It's not absolutely guaranteed that
they will have a TEMP variable but I suspect that there are few users
with recent versions of Windows who do not.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[David Horowitz]

Hi,
What you're looking to do seems completely valid to me.
If it were me, my solution would be to put the word lists in an Excel file
for easy maintainability. I would format it just like you list it, with two
columns.
Then in my code I'd open this Excel file using ADO - you can find many
references on the Internet as to how to open an Excel file via ADO.
Then I'd have a loop which would do a find and replace, searching for the
first column and replacing with the second column. You're using ADO
Recordsets for this in ADO. So roughly, the code looks like this:
Sub MyIncredibleFindAndReplace()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "connection string as per ADO instructions"
rs.Open cn, "query string as per ADO instructions"
Do While Not rs.EOF
ActiveDocument.Range.Find.Execute FindText:=rs(0), ReplaceWith:=rs(1),
Replace:=wdReplaceAll
rs.MoveNext
End Do
rs.Close
cn.Close
End Sub
Something like that - I didn't test that code.
HTH, post again for more help.
David

 

[Graham Mayor]

As I usually have more time on my hands than does Greg, I have offered and
it has been agreed that I will produce a 2007 version of Greg's add-in over
the weekend (or possibly earlier) though knowing Greg, if he has any time on
his hands he may jump the gun and do it himself ,

The issue of where to place settings files such as this is a perennial
problem. A logical place is the root of the C drive, but not all corporate
users have write access to the C drive root, and with the
http://www.gmayor.com/individual_merge_letters.htm add-in which Doug Robbins
and I collaborated on, after much user feedback, I chose to adopt the user's
My Documents folder, which requires a more complex function to properly
evaluate (that you can see in the code of the merge add-in).

Unlike the Temp folder which may be volatile, the My Documents folder should
be a pretty robust storage location - though I am always open to suggestions
for a better location. I personally would have preferred the user's Word
startup folder which contains the add-in itself, but again not all corporate
users have write access to this folder. The less elegant choice is to
allow the user to select the location, but then you have to decide if or
where to store the reference to that location ... aaaaagh!

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>