0

I have to replace around 1000 words/phrases in Word from Excel string (column A by column B). I am using the code below. It works well for a plain text, but does not work for tables embedded in the Word file: if the word to be replaced is in the table, the code replaces this word and creates many raws in the table not replacing all other words. How could I fix it? Thanks.

Sub findandreplace()

Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object

Dim i As Integer, j As Integer
Dim lastRow As Integer

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("PATH TO EXCEL FILE")
Set xlWS = xlWB.Worksheets("Sheet1") 'Replace String with your Worksheet Name

lastRow = 1000

For i = 1 To ThisDocument.Words.Count - 1 Step 1
For j = 1 To lastRow Step 1
        ThisDocument.Words(i) = Replace(ThisDocument.Words(i), xlWS.Cells(j, 1).Value, xlWS.Cells(j, 2).Value)
    Next j
Next i

Set xlWS = Nothing
xlWB.Close True
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing

End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Tanya Shulha
  • 1

1 Answers1

0

Your code working for all words which in Word are: text words, commas, dots, paragraph marks, end of cell marks and many others. You should try to use Find...Replace feature instead. But if you want to stay with your code the quickes idea I could think of would be to add additional If Statement like this:

....
For j = 1 To lastRow Step 1
    If InStr(1, ThisDocument.Words(i), xlWS.Cells(j, 1).Value, vbTextCompare) <> 0 Then
        ThisDocument.Words(i) = Replace(ThisDocument.Words(i), xlWS.Cells(j, 1).Value, xlWS.Cells(j, 2).Value)
    End if
Next j
....
Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
  • Kazimierz Jawor, thank you for your suggestion. What would be the code if I use Find...Replace? I am very new in VBA thought have to replace 1550 phrases asap for translation purpose. – Tanya Shulha Dec 12 '17 at 00:18
  • try to follow [this example](https://stackoverflow.com/questions/22187909/word-vba-find-replace-issue) or [this](https://stackoverflow.com/questions/18609963/vba-to-find-and-replace-a-text-in-ms-word-2010) or [this](https://wordmvp.com/FAQs/Customization/ReplaceAnywhere.htm) or [this](https://www.datanumen.com/blogs/4-useful-methods-find-replace-text-part-word-document/). As you can see there are a lot of examples in Internet. Moreover, you could record a macro to get the code. – Kazimierz Jawor Dec 12 '17 at 07:09