MS Office Forum / Word / Programming / July 2007
Multiple Search And Replace
|
|
Thread rating:  |
AHJ - 20 May 2007 17:38 GMT Group, Please assist me in my first use of VBA using MS Word. I have a very large text file that Word can open. We use an application that allows us to test multiple automotive radios. It creates a file made up of text. These radios change part numbers constantly. When a change occurs we have to make changes throughout this text file. For instance, Software ID "0x78AB", where values in " " are what change. But this is one of many changes, ie: Flash ID "0xACD5", Part Number "3238313031313234" etc. My vision of this application would be a separate file with a table that has all the changes in it. Launch a VBA macros which takes from one file and searches/replaces in the large text file. I hope this makes sense. Please point me in the right direction if Word isn't the best way to do this. If Word is, how do I do it. Thanks very much.
Tony
Doug Robbins - Word MVP - 20 May 2007 18:31 GMT Easier if you create the list of changes in a two column table in a Word Document with the old part numbers in the first column starting with the second row and the new part numbers in the corresponding cells in the second column. Then if you save that document and run the following macro when the document that you want to modify is the active document, it should then do what you want. Note, you will need to replace the "C:\Test\changes.doc" in the following line of the code:
Set ChangeDoc = Documents.Open("C:\Test\changes.doc")
with the drive\path and filename of the document containing the table of changes
Dim ChangeDoc As Document, RefDoc As Document Dim ctable As Table Dim oldpart As Range, newpart As Range Dim i As Long
Set RefDoc = ActiveDocument Set ChangeDoc = Documents.Open("C:\Test\changes.doc") Set ctable = ChangeDoc.Tables(1) For i = 2 To ctable.Rows.Count oldpart = ctable.Cell(i, 1).Range oldpart.End = oldpart.End - 1 newpart = ctable.Cell(i, 2).Range newpart.End = newpart.End - 1 Selection.HomeKey wdStory Selection.Find.ClearFormatting With Selection.Find .Execute findText:=oldpart, ReplaceWith:=newpart, Replace:=wdReplaceAll, MatchWildcards:=False, Forward:=True, Wrap:=wdFindContinue End With Next i ChangeDoc.Close wdDoNotSaveChanges
 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
> Group, > Please assist me in my first use of VBA using MS Word. I have a very [quoted text clipped - 11 lines] > > Tony AHJ - 23 May 2007 02:30 GMT Doug/Graham, At the line Set ctable = ChangeDoc.Tables(1) I get the following message
Run-time error '5941'
The requested member of the collection does not exist
Please assist, I'm a novice at VBA for Word.
Tony
Sub ChangeIt()
Dim ChangeDoc As Document, RefDoc As Document Dim ctable As Table Dim oldpart As Range, newpart As Range Dim i As Long Dim a As Long
Set RefDoc = ActiveDocument Set ChangeDoc = Documents.Open("C:\Documents and Settings\AHJ\Desktop\WordMacro\DocToBeChanged.txt")
Set ctable = ChangeDoc.Tables(1)
RefDoc.Activate
For i = 2 To ctable.Rows.Count Set oldpart = ctable.Cell(i, 1).Range oldpart.End = oldpart.End - 1 Set newpart = ctable.Cell(i, 2).Range newpart.End = newpart.End - 1 Selection.HomeKey wdStory Selection.Find.ClearFormatting With Selection.Find .Execute findText:=oldpart, ReplaceWith:=newpart, Replace:=wdReplaceAll, MatchWildcards:=False, Forward:=True, Wrap:=wdFindContinue End With Next i
ChangeDoc.Close wdDoNotSaveChanges
End Sub
> Group, > Please assist me in my first use of VBA using MS Word. I have a very [quoted text clipped - 11 lines] > > Tony Doug Robbins - Word MVP - 23 May 2007 04:54 GMT Have you create a Word document containing a two column table with headings in the first row and the items that you want changed in the second and subsequent rows of the first column and the items that you want them changed to in the corresponding cells of the second Column?
Have you saved and closed that document?
Have you modified the following line of code so that the path and filename are the same as that given to the document referred to above?
Set ChangeDoc = Documents.Open("C:\Test\changes.doc")
On my system, I have a folder in the root directory with the name of "Test" and I had saved the document containing the changes with the filename of "changes"
 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/Graham, > At the line Set ctable = ChangeDoc.Tables(1) I get the following message [quoted text clipped - 56 lines] >> >> Tony JE McGimpsey - 23 May 2007 04:56 GMT You're opening a text file (.txt), which by definition has no object structure, so there are no table objects to set the ctable variable to.
If your text file is delimited, or with fixed field sizes, you may be able to convert the text into a table with the ConvertToTable method (try recording a macro doing it manually).
> At the line Set ctable = ChangeDoc.Tables(1) I get the following message > > Run-time error '5941' > > The requested member of the collection does not exist AHJ - 25 May 2007 01:29 GMT Doug/Graham, I works like a champ. What I didn't do correctly was embed the macro in the file that had to be changed. The file with the table in it, I thought originally had to have the macro. Pretty impressive. This is going to save me a ton of work in our lab. Thanks very much.
Tony
> Group, > Please assist me in my first use of VBA using MS Word. I have a very [quoted text clipped - 11 lines] > > Tony Graham Mayor - 25 May 2007 06:13 GMT The macro doesn't have to be 'embedded in the file with the table' but it does have to be run with that file open.
 Signature <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP
My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
> Doug/Graham, > I works like a champ. What I didn't do correctly was embed the [quoted text clipped - 18 lines] >> Please point me in the right direction if Word isn't the best way to >> do this. If Word is, how do I do it. Thanks very much. Tony Doug Robbins - Word MVP - 25 May 2007 12:17 GMT When the macro is run, the document in which the changes are to be made must be the active document. The macro thenopens the file that contains the table of changes that are to be made.
 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
> The macro doesn't have to be 'embedded in the file with the table' but it > does have to be run with that file open. [quoted text clipped - 21 lines] >>> Please point me in the right direction if Word isn't the best way to >>> do this. If Word is, how do I do it. Thanks very much. Tony Graham Mayor - 25 May 2007 12:29 GMT What you added was what I meant, but somehow it didn't come out right. :(
The point I was trying to make was that the macro does not have to be 'embedded' in either document. It should be stored in a template - in this case in normal.dot so that it is always available.
 Signature <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP
My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<>
> When the macro is run, the document in which the changes are to be > made must be the active document. The macro thenopens the file that [quoted text clipped - 33 lines] >>>> Please point me in the right direction if Word isn't the best way >>>> to do this. If Word is, how do I do it. Thanks very much. Tony tezza98@gmail.com - 16 Jul 2007 04:51 GMT > What you added was what I meant, but somehow it didn't come out right. :( > [quoted text clipped - 48 lines] > >>>> Please point me in the right direction ifWordisn't the best way > >>>> to do this. IfWordis, how do I do it. Thanks very much. Tony Hi i was using this code to change multiple words in my document, and noticed that when you run the macro it only changes one instance of each word i want to change. As you might well guess this is a bit of a problem because i will havet orun the macro x amount of times to change multiple instances of the same word.
But i have come up with a solution - count all the instances of each word to replace and then using the count loop through the code
Public Sub change()
Dim ChangeDoc As Document, RefDoc As Document Dim ctable As Table Dim oldpart As Range, newpart As Range Dim i As Long Dim iCount As Integer Dim j As Integer
Set RefDoc = ActiveDocument Set ChangeDoc = Documents.Open("C:\Documents and Settings\terryh \Desktop\New Folder\ud.xls") Set ctable = ChangeDoc.Tables(1) RefDoc.Activate For i = 1 To ctable.Rows.Count Set oldpart = ctable.Cell(i, 1).Range oldpart.End = oldpart.End - 1 Set newpart = ctable.Cell(i, 2).Range newpart.End = newpart.End - 1 Selection.HomeKey wdStory Selection.Find.ClearFormatting
iCount = 0 Application.ScreenUpdating = False With Selection .HomeKey Unit:=wdStory With .Find .ClearFormatting .Text = oldpart ' Loop until Word can no longer ' find the search string and ' count each instance Do While .Execute iCount = iCount + 1 Selection.MoveRight Loop End With End With ' show the number of occurences
For j = 0 To iCount With Selection.Find .Execute findText:=oldpart, ReplaceWith:=newpart, MatchWildcards:=True, Wrap:=wdFindContinue, Forward:=True, MatchWholeWord:=False End With Next j
Next i ChangeDoc.Close wdDoNotSaveChanges
End Sub
I hope this helps people out there who dont want o pay top dollar for simple find and replace programs
Doug Robbins - Word MVP - 16 Jul 2007 06:51 GMT To which code are you referring?
The following will replace each instance of a word:
Dim ChangeDoc As Document, RefDoc As Document Dim ctable As Table Dim oldpart As Range, newpart As Range Dim i As Long
Set RefDoc = ActiveDocument Set ChangeDoc = Documents.Open("C:\Test\changes.doc") Set ctable = ChangeDoc.Tables(1) RefDoc.Activate For i = 2 To ctable.Rows.Count Set oldpart = ctable.Cell(i, 1).Range oldpart.End = oldpart.End - 1 Set newpart = ctable.Cell(i, 2).Range newpart.End = newpart.End - 1 Selection.HomeKey wdStory Selection.Find.ClearFormatting With Selection.Find .Execute findText:=oldpart, ReplaceWith:=newpart, Replace:=wdReplaceAll, MatchWildcards:=False, Forward:=True, Wrap:=wdFindContinue End With Next i ChangeDoc.Close wdDoNotSaveChanges
 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
>> What you added was what I meant, but somehow it didn't come out right. :( >> [quoted text clipped - 114 lines] > I hope this helps people out there who dont want o pay top dollar for > simple find and replace programs
|
|
|