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

Is the file .XLS or .XLSX? The two are quite different.

ashleedawg
– ashleedawg

2018-10-04 07:56:08 +00:00
Commented Oct 4, 2018 at 7:56 — ashleedawg
– ashleedawg, Commented Oct 4, 2018 at 7:56
file extension is .XLS

user3189644
– user3189644

2018-10-04 09:14:12 +00:00
Commented Oct 4, 2018 at 9:14 — user3189644
– user3189644, Commented Oct 4, 2018 at 9:14

jkpieterse · Accepted Answer · 2018-10-04 09:19:59Z

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

answered Oct 4, 2018 at 9:19

jkpieterse

3,0391 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vityata · Accepted Answer · 2018-10-04 09:29:49Z

1

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.

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.

edited Oct 4, 2018 at 9:29

answered Oct 4, 2018 at 8:05

Vityata

43.7k10 gold badges64 silver badges110 bronze badges

2 Comments

user3189644 Over a year ago

Yes, Microsoft Word 16.0 Object Library is missing from References in Excel 2010. It's causing the error but I don't know how to use late binding in my code

Vityata Over a year ago

@user3189644 - I guess setting the object to ActiveDocument.Bookmarks("name").Range would work quite ok (taken from the code in the question).

Collectives™ on Stack Overflow

Excel VBA macro late binding

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related