Find & Replace From Excel Table

[NIMISH]

Hi,

I am sure it will be very easy for seasoned Gurus of Word & Excel.

I have excel table that has two columns

Column A : Contains Old number
Column B : Contains New Number

There are approximately 1250 rows in excel Spread sheet.

I want to search word document for 'Old Number' and replace with 'Old
Number / (New Number)', where 'Old Number' and 'New Number' are picked
up from Excel

So to summarize my need here are the steps I want to perform

1. Go in Excel
2. Find 'Old Number' from column A, find 'New Number' from column B
3 Go to Word
4 Perform Find and replace (as explained earlier)
5 Go in Excel
6 Go to next row and perform same thing from step 2 onwards above.

I am new to VBA and could not figure it out how to do it.

Thanks in advance

Nimish

 

[Doug Robbins - Word MVP]

See the article "Retrieving Data from a Named Range in Excel using DAO" at:

http://www.word.mvps.org/FAQs/InterDev/XLToWordWithDAO.htm

This should do it:

'Set a reference to the Microsoft DAO 3.6 Object Library under
Tools>References
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim oldnumber As String
Dim newnumber As String
Dim myrange As Range

'Change path\filename
Set db = OpenDatabase("C:\Test\Book1.xls", False, False, "Excel 8.0")
'In Excel, assign the name New for Old to the range of numbers, including
the column headings
Set rs = db.OpenRecordset("SELECT * FROM `NewforOld`")

While Not rs.EOF
oldnumber = rs.Fields(0).Value
newnumber = rs.Fields(1).Value
Selection.HomeKey wdStory
Selection.Find.ClearFormatting
With Selection.Find
Do While .Execute(FindText:=oldnumber, MatchWholeWord:=True,
MatchWildcards:=False, Wrap:=wdFindStop, Forward:=True) = True
Set myrange = Selection.Range
Selection.Collapse wdCollapseEnd
Selection.MoveRight wdWord, 1
myrange.Text = oldnumber & "/(" & newnumber & ")"
Loop
End With
rs.MoveNext
Wend

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing


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

 

[NIMISH]

That's great!

I will test with sample data.

Thanks,

Nimish