MS Office Forum / Word / Programming / September 2006
Macro for Mass Find and Replace
|
|
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.
|
|
|