Multiple Search And Replace

[AHJ]

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]

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


--
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 Robbins - Word MVP]

Sorry I missed a line of code and a couple of other things as I had not
tested it. Here is an amended version that I have run so it should be OK:

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



--
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

 

[Graham Mayor]

I am curious why you used
For i = 2 To ctable.Rows.Count
rather than
For i = 1 To ctable.Rows.Count
Does your table have a header row, as this would ignore the entries in the
first row?

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


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

 

[Doug Robbins - Word MVP]

Yes, I was assuming that there would be a header row.

--
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

 

[Graham Mayor]

OK - you had me worried that I was missing something there for a minute

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


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

 

[AHJ]

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

 

[Doug Robbins - Word MVP]

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"

--
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

 

[JE McGimpsey]

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).

 

[AHJ]

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

 

[Graham Mayor]

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.

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


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

 

[Doug Robbins - Word MVP]

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.

--
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

 

[Graham Mayor]

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.

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


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

 

[tezza98]

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 eitherdocument. It should be stored in a template - in this
case in normal.dot so that it is always available.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - WordMVP

My web sitewww.gmayor.comWordMVP web sitehttp://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

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]

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


--
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