CODEWITHSUNDEEP
excelvbams-wordlate-binding
user3189644
user3189644

Reputation: 177

Excel VBA macro late binding

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

Upvotes: 1

Views: 1013

Answers (2)

Vityata
Vityata

Reputation: 43565

When I'm trying to open XLS document in Excel 2010 and run the macro, I receive following error: "Compile error in hidden module1.

I cannot replicate, but I guess this is because in the original file there is a reference for xlsx - Microsoft Office 16 Object Library To see, Check VBEditor>Tools>References.

enter image description here

Late binding such as:

Dim wdObject As Object 
Set wdObject = CreateObject("Word.Application")

Dim orange as Object
set orange = ActiveDocument.Bookmarks("name").Range

could be a working solution.

Upvotes: 1

jkpieterse
jkpieterse

Reputation: 2956

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

Upvotes: 1

Related Questions