Word: need looped search & replace, but with a twist

[McGinley57]

I've been mulling this over for a day or two, and I'm just not experienced enough to get to an answer.

I have a Word file with a series of tables in it that present product prices. Column 1 is the product name, column 2 is product code and column 3 is price. In addition, I have an Excel file that is used to store all the prices in various currencies we sell in.

I've written VBA code in the Word doc to access the Excel file and retrieve prices in for a specific currency. The data item used for linking is the product code, which is specified in both files.

Now, here's what I want to do. For each product code in the Excel file, I want to search for a match in the Word file. When the search is successful, I want to move one cell to the right from the product code then replace the contents of the cell with the associated foreign currency price. I've written everything I need to establish communication between the two file and retrieve the prices. I can't perfect, however, a search and replace routine. If I use the Execute.Find method, I can find a product string and insert text into next cell, but I can't outright replace all the text in the cell. Something of an example of what I want to do can be found here:

http://support.microsoft.com/?kbid=211458

Instead of inserting the word "Tip", I want to move to the next cell and replace the contents (sorry for the redundancy).

If anyone can point me in the right direction, I'd appreciate it.

 

[SkipVought]

Hi,

Why are you using a word processing application to do the job that a spreadsheet application is suited for? It's like using a hammer to drive in a screw: might be a workaround, if the hammer were your only tool.

Short of a needed redesign, check out the ROW and COLUMN properties of the table object. When you FIND a product in a table, you have that table's ROW.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[fumei]

If I use the Execute.Find method, I can find a product string and insert text into next cell, but I can't outright replace all the text in the cell.

While agreeing with Skip, I am a little confused.

When the search is successful, I want to move one cell to the right from the product code then replace the contents of the cell with the associated foreign currency price.

One cell to the right in the Word file?


If so, then you could do:

Dim r As Range
On Error Resume Next
Set r = ActiveDocument.Tables(1).Range
With r.Find
   Do While .Execute(FindText:="FOUND!", Forward:=True) _
                = True
      r.Next(Unit:=wdCell, Count:=1).Text = _
         "The Next Cell"
      r.Collapse Direction:=wdCollapseEnd
   Loop
End With

This would go through table 1, and find each cell containing "FOUND!", and replace the next cell text with "The Next Cell".

You could of course append text, replace part...whatever you want really.

N.B. r.Collapse Direction:=wdCollapseEnd can also be written as:

r.Collapse 0

faq219-2884

Gerry
My paintings and sculpture

 

[McGinley57]

Thanks! That was the code I needed. Very nice.

I agree with both of you that this is not an efficient way to work, but I'm trying to help someone who has developed a very idiosyncratic price book over a number of years. It's full of annotations, footnotes, etc. Unfortunately, to keep our business running in the short-term, I needed to find a way that my co-worker could continue to work in the environment she's most comfortable with. The next step will be to migrate everything to XML.

Thanks again for your help, both Skip and Funai.

McGinley