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 / Document Management / March 2008

Tip: Looking for answers? Try searching our database.

How to replace multiple words with replacement words in Excel

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
jvr - 20 Mar 2008 22:31 GMT
How to replace multiple words with replacement words in Excel

In my Word documents, I must use temporary “Identifiers” (internal temporary
codes; supplied to me) that must be later replaced with actual “Identifiers”
(in an Excel file supplied by the customer much later and perhaps multiple
times).

The actual “Identifiers” in the Excel file are “paired” with my temporary
“Identifiers” (as two cells, side-by-side).

For example:
PTT-001          205-PTT-0924
PTT-124          205-PTT-0020
Meaning:
For each instance of PTT-001 replace with 205-PTT-0924,
for each instance of PTT-124 replace with 205-PTT-0020,
etc. (there may or may not be any particular pattern).

There will be hundreds of these “pairs” and each one may be in the document
zero, one, or more times.

Doing this manually with “Find and Replace” is extremely time-consuming.

Thanks so much in advance for any solution.

I really appreciate it.

jvr0308@yahoo.com
Graham Mayor - 21 Mar 2008 07:01 GMT
If  you copy the two columns of the Excel table to Word, and save it as a
Word document, with its path identified in place of  "D:\My
Documents\Test\changes.doc" in the line:-

sFname = "D:\My Documents\Test\changes.doc"

the following macro run on your document will replace all the items in the
first column with the corresponding items in the second column

Sub ReplaceFromTableList()

Dim ChangeDoc As Document, RefDoc As Document
Dim cTable As Table
Dim oldPart As Range, newPart As Range
Dim i As Long
Dim sFname As String

sFname = "D:\My Documents\Test\changes.doc"
Set RefDoc = ActiveDocument
Set ChangeDoc = Documents.Open(sFname)
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
   With Selection
       .HomeKey wdStory
       With .Find
           .ClearFormatting
           .Replacement.ClearFormatting
           .Execute findText:=oldPart, _
           ReplaceWith:=newPart, _
           Replace:=wdReplaceAll, _
           MatchWholeWord:=True, _
           MatchWildcards:=False, _
           Forward:=True, _
           Wrap:=wdFindContinue
       End With
   End With
Next i
ChangeDoc.Close wdDoNotSaveChanges
End Sub

Signature

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

My web site www.gmayor.com
Word MVP web site http://word.mvps.org
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

> How to replace multiple words with replacement words in Excel
>
[quoted text clipped - 25 lines]
>
> jvr0308@yahoo.com
 
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.