Find / replace text in embedded word object code stopped working

Question

I have used this code successfully to replace content in an embedded word object from excel. I copied the code for a new excel file but now it doesn't work. It opens the file but doesn't replace although I can see that it IS finding the right text and replacement text. I'm kind of lost as to what is happening.

 Dim strFindText As Range
  Dim strReplaceText As Range
  Dim nSplitItem As Long
  
 Set strFindText = ActiveWorkbook.Worksheets("Utilisation Form").Range("c11:c20")
Set strReplaceText = ActiveWorkbook.Worksheets("Utilisation Form").Range("a11:a20")
  nSplitItem = strFindText.Count
Debug.Print strFindText.Item(0)

For Each sh In ThisWorkbook.Sheets("Utilisation Form").Shapes
If sh.Name <> "Object 1" Then sh.Delete
Next


Set urobj = ThisWorkbook.Sheets("Utilisation Form").OLEObjects("Object 1")
Set wordtemp = urobj.Duplicate

wordtemp.Verb Verb:=xlOpen

Set wordtemp2 = wordtemp.Object


For x = 1 To nSplitItem
With wordtemp2.Content.Find
.Forward = True
.Text = strFindText.Item(x)
.ClearFormatting
 .Replacement.Text = strReplaceText.Item(x)
.Execute Replace:=wdReplaceAll
End With
Next x

End Sub

Thanks for the support

Have you tried to debug the code by running it under the debugger? Do you get any errors? — Eugene Astafiev, Jul 27 '22 at 12:37
@jonsson - yes, i have activated the reference to the word object library... — b0t, Jul 28 '22 at 09:07
@EugeneAstafiev - i debug.print the find and replace statements and it seems to be working (in terms of correctly finding the text). I also ran a boolean with msgbox to see if it would trigger in case of a false outcome but no msg box appeared — b0t, Jul 28 '22 at 09:10
Don't use message boxes for checking results in the code. I'd suggest using [Debug.Print](https://stackoverflow.com/questions/2916287/where-does-vba-debug-print-log-to) instead. — Eugene Astafiev, Jul 28 '22 at 10:10
@jonsson I looked again and realized that you were right about the Word Object library. It had been disactivated after I activated it! Found out via checking the wdReplaceAll. Thanks so much both! — b0t, Jul 29 '22 at 10:44

score 0 · Answer 1 · answered Jul 27 '22 at 12:36

When the early-binding technology is used in the code you need to add a corresponding COM reference to be able to use data types. Otherwise, you need to declare everything from the Word object model as Object in the code and use the late-binding technology.

To use early binding on an object, you need to know what its v-table looks like. In Visual Basic, you can do this by adding a reference to a type library that describes the object, its interface (v-table), and all the functions that can be called on the object. Once that is done, you can declare an object as being a certain type, then set and use that object using the v-table. For example, if you wanted to Automate Microsoft Office Excel using early binding, you would add a reference to the Microsoft Excel X.0 Object Library from the Project|References dialog, and then declare your variable as being of the type Excel.Application. From then on, all calls made to your object variable would be early bound.

Read more about that in the Using early binding and late binding in Automation article.

Find / replace text in embedded word object code stopped working

1 Answers1