Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / September 2006

Tip: Looking for answers? Try searching our database.

Macro for Mass Find and Replace

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jjacobs13@gmail.com - 27 Aug 2006 17:54 GMT
I am looking for a way to emulate the Find & Replace function in Word.
However, I would like to Find multiple selections and uniquely Replace
each one (after having pasted them each on a new line at the beginning
of the document/template).

For example, I have the word "Bob" and "Jon", and I would like to
replace each occurrence with "George" and "Fred", respectively.

I have the following at the beginning of the document:

Bob
Jon
George
Fred

How would I go about coding a small script for a Macro that would cut
each line and use every two in a Find & Replace function?  (Perhaps
even have a textbox appear at the start of the Macro that would request
the number of Find & Replace items.)
jjacobs13@gmail.com - 27 Aug 2006 17:56 GMT
I guess the words don't have to be pasted at the beginning of the
document.  Perhaps just copied/pulled from another Word document.
Doug Robbins - Word MVP - 27 Aug 2006 20:49 GMT
Use the following type of construction:

Dim vFindText As Variant
Dim vReplText As Variant
Dim sFindText As String
Dim sReplText As String
Dim i As Long

vFindText = Array("one", "two", "three", "four", "five")
vReplText = Array("six", "seven", "eight", "nine", "ten")
For i = LBound(vFindText) To UBound(vFindText)
   sFindText = vFindText(i)
   sReplText = vReplText(i)
   With Selection.Find
       .Forward = True
       .Wrap = wdFindContinue
       .MatchWholeWord = True
       .MatchWildcards = False
       .MatchSoundsLike = False
       .MatchAllWordForms = False
       .Format = True
       .MatchCase = False
       .Text = sFindText
       .replacement.Text = sReplText
       .Execute Replace:=wdReplaceAll
   End With
Next i

Or you could put the words to be found in one column of a table and
replacements words in the second column and then iterate through the rows of
the tables, getting the words our of each column and using them for the
sFindText and sReplText in the above.
Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

>I guess the words don't have to be pasted at the beginning of the
> document.  Perhaps just copied/pulled from another Word document.
Ann Marie - 28 Aug 2006 03:28 GMT
Doug was saying,

Or you could put the words to be found in one column of a table and
replacements words in the second column and then iterate through the rows of
the tables, getting the words our of each column and using

--How would  you use the table option Doug?
If I have 3 different searches I always did en masse could I ask to call the
.doc file with table.
I am very new at VBA - how would I code this based on the code below and
asking my users which document they would like to call and then running the
below macro?
I sometimes have 4 or 5 lines in my replacement text is that an issue?

Would appreciate some assisstance in how to set up a routine.  It sounds
great...

> Use the following type of construction:
>
[quoted text clipped - 30 lines]
>>I guess the words don't have to be pasted at the beginning of the
>> document.  Perhaps just copied/pulled from another Word document.
Doug Robbins - Word MVP - 28 Aug 2006 20:47 GMT
Hi Ann Marie,

Here is the code that I would use.  It assumes that the table of words to
find and their replacements is in a document with the filename of source.doc
and in my case, it is located in a folder c:\Documents.  You will need to
modify that line of code to suit your setup.  It also assumes that the first
row of the table in that document contains a header row, so that the first
word to be found is located in the second row of the table.

Sub ReplaceList()
Dim vFindText As Variant
Dim vReplText As Variant
Dim sFindText As Range
Dim sReplText As Range
Dim i As Long
Dim Source As Document
Dim Target As Document
Set Target = ActiveDocument 'The document in which the replacements are to
be made
Set Source = Documents.Open("c:\documents\source.doc") 'Open the document
containing the table of
'replacements to be made. It assumes that there is a header row in the
table. 'Modify the path and filename to suit.
With Source.Tables(1)
   For i = 2 To .Rows.Count
       Set sFindText = .Cell(i, 1).Range
       sFindText.End = sFindText.End - 1
       Set sReplText = .Cell(i, 2).Range
       sReplText.End = sReplText.End - 1
       Target.Activate
       With Selection.Find
           .Forward = True
           .Wrap = wdFindContinue
           .MatchWholeWord = True
           .MatchWildcards = False
           .MatchSoundsLike = False
           .MatchAllWordForms = False
           .Format = True
           .MatchCase = False
           .Text = sFindText
           .replacement.Text = sReplText
           .Execute Replace:=wdReplaceAll
       End With
   Next i
End With
Source.Close wdDoNotSaveChanges
Target.Activate

End Sub

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Doug was saying,
>
[quoted text clipped - 48 lines]
>>>I guess the words don't have to be pasted at the beginning of the
>>> document.  Perhaps just copied/pulled from another Word document.
Ann Marie - 29 Aug 2006 08:59 GMT
Hi Doug, that was AWESOME to say the least!

If I could ask another question. I have remmed out the code I want to add -
but not sure I can do this?

1. Run SET SOURCE as a default setting ALWAYS. Permanently set it to run. SO
run your macro with default source.

2. But then ask the user for a NEW source document which is optional and run
the code again with that document name (same path) just new name c:\
"xxxxxx".doc where "xxxxxx" is variable?

Doug, what would be best way to code that?  Do I run the macro and call  a
duplicate with the Input command?

"Doug Robbins - Word MVP">
> Here is the code that I would use.  It assumes that the table of words to
> find and their replacements is in a document with the filename of
[quoted text clipped - 4 lines]
>
> Sub ReplaceList()

'   Dim strUserData As String

> Dim vFindText As Variant
> Dim vReplText As Variant
[quoted text clipped - 5 lines]
> Set Target = ActiveDocument 'The document in which the replacements are to
> be made

' strUserData = InputBox("Type file name? c:\, "File Name", "")

> Set Source = Documents.Open("c:\strUserData & ".doc"") 'Open the document
> containing the table of 'replacements to be made. It assumes that there is
[quoted text clipped - 78 lines]
>>>>I guess the words don't have to be pasted at the beginning of the
>>>> document.  Perhaps just copied/pulled from another Word document.
Doug Robbins - Word MVP - 29 Aug 2006 21:27 GMT
The following modified code will display a message box that allows the user
to select either the default file containing the replacements or if they
click on the No button, it will display the File>Open dialog so that they
can select a file to be used.

Sub ReplaceList()
Dim vFindText As Variant
Dim vReplText As Variant
Dim sFindText As Range
Dim sReplText As Range
Dim i As Long
Dim SourceFile As String
Dim Source As Document
Dim Target As Document
Set Target = ActiveDocument 'The document in which the replacements are to
be made
Dim Msg, Style, Title, Response
Msg = "Do you want to use the default replacements file ?"    ' Define
message.
Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
Title = "Select Source File"    ' Define title.
       ' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes.
   Set Source = Documents.Open("c:\documents\source.doc") 'Open the
document containing the table of
'replacements to be made. It assumes that there is a header row in the
table.
'Modify the path and filename to suit.
Else    ' User chose No. Display the FileOpen dialog
   With Dialogs(wdDialogFileOpen)
       If .Display <> -1 Then
           SourceFile = ""
           MsgBox "You did not select a file."
           Exit Sub
       Else
           SourceFile = WordBasic.FileNameInfo$(.Name, 1)
       End If
       Set Source = Documents.Open(SourceFile) 'Open the selected document
   End With
End If

With Source.Tables(1)
   For i = 2 To .Rows.Count
       Set sFindText = .Cell(i, 1).Range
       sFindText.End = sFindText.End - 1
       Set sReplText = .Cell(i, 2).Range
       sReplText.End = sReplText.End - 1
       Target.Activate
       With Selection.Find
           .Forward = True
           .Wrap = wdFindContinue
           .MatchWholeWord = True
           .MatchWildcards = False
           .MatchSoundsLike = False
           .MatchAllWordForms = False
           .Format = True
           .MatchCase = False
           .Text = sFindText
           .replacement.Text = sReplText
           .Execute Replace:=wdReplaceAll
       End With
   Next i
End With
Source.Close wdDoNotSaveChanges
Target.Activate

End Sub

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Hi Doug, that was AWESOME to say the least!
>
[quoted text clipped - 118 lines]
>>>>>I guess the words don't have to be pasted at the beginning of the
>>>>> document.  Perhaps just copied/pulled from another Word document.
Jen - 30 Aug 2006 10:27 GMT
Perfect  Doug.
> The following modified code will display a message box that allows the
> user to select either the default file containing the replacements or if
[quoted text clipped - 188 lines]
>>>>>>I guess the words don't have to be pasted at the beginning of the
>>>>>> document.  Perhaps just copied/pulled from another Word document.
Ann Marie - 29 Aug 2006 10:19 GMT
Hi Doug,

I ran into a slight hitch! I have an inline graphics but all I get is a
square box outline when I run your code. Is it possible to insert an inline
graphic do you know?

Ann

> Hi Ann Marie,
>
[quoted text clipped - 98 lines]
>>>>I guess the words don't have to be pasted at the beginning of the
>>>> document.  Perhaps just copied/pulled from another Word document.
jjacobs13@gmail.com - 30 Aug 2006 22:12 GMT
How does one go about copying the words into the Array (from another
document and/or pasted into the beginning)?

Thank you for sharing your knowledge.

> Use the following type of construction:
>
[quoted text clipped - 38 lines]
> >I guess the words don't have to be pasted at the beginning of the
> > document.  Perhaps just copied/pulled from another Word document.
Greg Maxey - 30 Aug 2006 23:18 GMT
Say you had a two column table with headings "Find" and "Replace" in a
document named C:\Word List.doc

Sub FillArray()
Dim myArray() As String
Dim oDoc As Word.Document
Dim i As Long
Dim j As Long
Dim pRow As Long
Dim oTbl As Word.Table
Set oDoc = Documents.Open("C:\Word List.doc")
Set oTbl = oDoc.Tables(1)
ReDim myArray(oTbl.Rows.Count - 2, 1) 'Size the array
For pRow = 2 To oTbl.Rows.Count 'Start with 2 to skip the header row
 j = j + 1 'For testing only
 For i = 1 To 2
   myArray(pRow - 2, i - 1) = Left(oTbl.Cell(pRow, i), _
         Len(oTbl.Cell(pRow, i).Range.Text) - 2)
 Next
Next
oDoc.Close
'Testing
For pRow = 0 To j - 1
 For i = 0 To 1
   MsgBox myArray(pRow, i)
 Next
Next
End Sub
Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> How does one go about copying the words into the Array (from another
> document and/or pasted into the beginning)?
[quoted text clipped - 43 lines]
>>> I guess the words don't have to be pasted at the beginning of the
>>> document.  Perhaps just copied/pulled from another Word document.
Ann Marie - 31 Aug 2006 09:57 GMT
Hi Greg,
I'm a little bedazzled by that one - yes it loads the array and messages me
but how does one call that  as
vFindText
vReplace
using Doug's code as find and replace?

> Say you had a two column table with headings "Find" and "Replace" in a
> document named C:\Word List.doc
[quoted text clipped - 71 lines]
>>>> I guess the words don't have to be pasted at the beginning of the
>>>> document.  Perhaps just copied/pulled from another Word document.
Greg Maxey - 31 Aug 2006 12:42 GMT
Ann Marie,

I didn't test in Doug's code, but to use it in Find and Replace you
would do something like:

Sub FillArrayThenFindandReplace()
Dim myArray() As String
Dim oDoc As Word.Document
Dim i As Long
Dim j As Long
Dim pRow As Long
Dim oTbl As Word.Table
Dim oRng As Word.Range
Set oDoc = Documents.Open("C:\Word List.doc")
Set oTbl = oDoc.Tables(1)
ReDim myArray(oTbl.Rows.Count - 2, 1) 'Size the array
For pRow = 2 To oTbl.Rows.Count 'Start with 2 to skip the header row
 For i = 1 To 2
   myArray(pRow - 2, i - 1) = Left(oTbl.Cell(pRow, i), _
         Len(oTbl.Cell(pRow, i).Range.Text) - 2)
 Next
Next
oDoc.Close
Set oRng = ActiveDocument.Range
For pRow = 0 To UBound(myArray)
 With oRng.Find
   .Text = myArray(pRow, 0)
   .Replacement.Text = myArray(pRow, 1)
   .Execute Replace:=wdReplaceAll
  End With
Next
End Sub

I have posted an Addin on my website that I created with the help of
Doug and others that you might be interested in:

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

> Hi Greg,
> I'm a little bedazzled by that one - yes it loads the array and messages me
[quoted text clipped - 84 lines]
> >>>> I guess the words don't have to be pasted at the beginning of the
> >>>> document.  Perhaps just copied/pulled from another Word document.
Ann Marie - 31 Aug 2006 15:25 GMT
Yes I have your add-in Greg - it is really super. I have lots of your
add-ins.

But I needed to set up a specific code routine for a group of templates.
Most grateful for the help from yourself and Doug - I just would not have
got nearly this far in such a short time.

Will put this code to good use. Add my extra find and replace routines and a
bit more testing and wallah!!

Ann

> Ann Marie,
>
[quoted text clipped - 123 lines]
>> >>>> I guess the words don't have to be pasted at the beginning of the
>> >>>> document.  Perhaps just copied/pulled from another Word document.
Ann Marie - 31 Aug 2006 15:43 GMT
Just did test Greg- it flys. The array is so quick. Really, thank you both.

I have a small issue with returns on a couple of arrays but arrays are not
meant to have carriage returns (as far as I am aware) so will work around
it.

Ann

> Yes I have your add-in Greg - it is really super. I have lots of your
> add-ins.
[quoted text clipped - 136 lines]
>>> >>>> I guess the words don't have to be pasted at the beginning of the
>>> >>>> document.  Perhaps just copied/pulled from another Word document.
Ann Marie - 31 Aug 2006 15:35 GMT
Hi Greg,

I thought you might like to know that I also use Beta 2 2007 but for some
reason (beyond me) I cannot see your add-in in ADD-INS (VBA Find and
Replace - very cool) ribbon.

I use it in 2003 and it is fine - so it should load as my other add-ins do
when  I load 2007 Word.  It says it is loaded but it does not show in Ribbon
in ADD-INS?

Any ideas why that might be happening? I could move the code across I guess
and add to my global template - may be that will help.

Off to give this code a whirl and get this routine into test mode with my
templates in 2003.

Again, a huge thank you - they have "speed reading" and now "speed vba" with
the help of yourself Greg and Doug.

Enjoy your day ....

> Ann Marie,
>
[quoted text clipped - 123 lines]
>> >>>> I guess the words don't have to be pasted at the beginning of the
>> >>>> document.  Perhaps just copied/pulled from another Word document.
Laron Miyashiro - 31 Aug 2006 19:55 GMT
wow

On 8/31/06 4:35 AM, in article eo6$DrQzGHA.4392@TK2MSFTNGP04.phx.gbl, "Ann
Marie" <ann@microsoft.com> wrote:

> Hi Greg,
>
[quoted text clipped - 144 lines]
>>>>>>> I guess the words don't have to be pasted at the beginning of the
>>>>>>> document.  Perhaps just copied/pulled from another Word document.
Greg Maxey - 31 Aug 2006 22:21 GMT
Ann Marie,
I get "VBA Find and Replace User Interface" in the "Custom Toolbar" group of
the Addin Ribbon whenever I load the VBA Addin using Word2007 BETA 2.

I don't know why it won't work for you.

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> Hi Greg,
>
[quoted text clipped - 145 lines]
>>>>>>> the document.  Perhaps just copied/pulled from another Word
>>>>>>> document.
Ann Marie - 01 Sep 2006 12:59 GMT
Yes, it is quite strange.

> Ann Marie,
> I get "VBA Find and Replace User Interface" in the "Custom Toolbar" group
[quoted text clipped - 151 lines]
>>>>>>>> the document.  Perhaps just copied/pulled from another Word
>>>>>>>> document.
jjacobs13@gmail.com - 01 Sep 2006 00:29 GMT
That's great.

Two more questions:

1. How can the document (with the information) be selected on the fly
instead of having it set before?

2. How can the formatting be retained (i.e. bold, italicized, etc)?

Thanks once again.  This is quite useful for me.

> Say you had a two column table with headings "Find" and "Replace" in a
> document named C:\Word List.doc
[quoted text clipped - 77 lines]
> >>> I guess the words don't have to be pasted at the beginning of the
> >>> document.  Perhaps just copied/pulled from another Word document.
Doug Robbins - Word MVP - 31 Aug 2006 04:46 GMT
Or just use the method in my message of 8/28/2006 that iterates through the
data in a table.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> How does one go about copying the words into the Array (from another
> document and/or pasted into the beginning)?
[quoted text clipped - 44 lines]
>> >I guess the words don't have to be pasted at the beginning of the
>> > document.  Perhaps just copied/pulled from another Word document.
Jen - 31 Aug 2006 08:42 GMT
But it won't insert my graphic. Am I able to add extra code when it selects
the col 2 replace text to check to see if there is a graphic or inline
object?

> Or just use the method in my message of 8/28/2006 that iterates through
> the data in a table.
[quoted text clipped - 47 lines]
>>> >I guess the words don't have to be pasted at the beginning of the
>>> > document.  Perhaps just copied/pulled from another Word document.
Greg Maxey - 01 Sep 2006 01:11 GMT
For question 1, you could use:

Sub FillArray()
Dim myArray() As String
Dim oDoc As Word.Document
Dim i As Long
Dim j As Long
Dim pRow As Long
Dim oTbl As Word.Table
Dim myFile As String
Dim PathToUse As String
With Dialogs(wdDialogFileOpen)
 If .Show <> 0 Then
   Set oDoc = ActiveDocument
   Set oTbl = oDoc.Tables(1)
   ReDim myArray(oTbl.Rows.Count - 2, 1) 'Size the array
   For pRow = 2 To oTbl.Rows.Count 'Start with 2 to skip the header row
     j = j + 1 'For testing only
     For i = 1 To 2
       myArray(pRow - 2, i - 1) = Left(oTbl.Cell(pRow, i), _
         Len(oTbl.Cell(pRow, i).Range.Text) - 2)
     Next
   Next
   oDoc.Close
 Else
   MsgBox "No file was opened."
 End If
End With
'Testing
For pRow = 0 To j - 1
 For i = 0 To 1
   MsgBox myArray(pRow, i)
 Next
Next
End Sub

AFAIK, you can't load formatting into an array.

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> How does one go about copying the words into the Array (from another
> document and/or pasted into the beginning)?
[quoted text clipped - 43 lines]
>>> I guess the words don't have to be pasted at the beginning of the
>>> document.  Perhaps just copied/pulled from another Word document.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.