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 / Programming / May 2006

Tip: Looking for answers? Try searching our database.

Find & Replace From Excel Table

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
NIMISH - 26 May 2006 19:21 GMT
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 - 27 May 2006 09:20 GMT
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

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,
>
[quoted text clipped - 25 lines]
>
> Nimish
NIMISH - 29 May 2006 23:38 GMT
That's great!

I will test with sample data.

Thanks,

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