Excel VBA macro late binding

Question

I have embedded Word document in Excel file. I use the macro below to copy cell value from Excel to embedded Word document bookmark. Macro works fine in Excel 2016. The problem is that I need to use this in macro in several Excel versions. When I'm trying to open XLS document in Excel 2010 and run the macro, I receive following error: "Compile error in hidden module1. This error commonly occurs when code is incompatible with the version..." I think I have to translate my code to use Late binding so that it's not using VBA Project references. Please help!

Sub update_bookmark()

Dim oRng As Word.Range

 Set objWordTemplate = Sheets("Form1").OLEObjects("Object 1")
 objWordTemplate.Activate
 objWordTemplate.Object.Application.Visible = True
 Worksheets("source_sheet").Activate

 Set oRng = ActiveDocument.Bookmarks("name").Range
 oRng.Text = Cells(Application.ActiveCell.Row, 2)
 ActiveDocument.Bookmarks.Add "name", oRng

 End

End Sub

Answer 1

Uncheck the Word reference and update your code like so:

Sub update_bookmark()

Dim oRng As Object
Dim objWordTemplate as Object
 Set objWordTemplate = Sheets("Form1").OLEObjects("Object 1")
 objWordTemplate.Activate
 objWordTemplate.Object.Application.Visible = True
 Worksheets("source_sheet").Activate

 Set oRng = objWordTemplate.Object.Application.ActiveDocument.Bookmarks("name").Range
 oRng.Text = Cells(Application.ActiveCell.Row, 2)
 objWordTemplate.Object.Application.ActiveDocument.Bookmarks.Add "name", oRng

End Sub