VBA Late Binding – For userforms based on user computers that do not have Word References

By | December 17, 2017
Questions:

I have an excel userform/worksheet that opens a Word document/template and saves that Word doc as a PDF, with a specific filename, in the same folder.

In my references on Excel I have Word Object Library enabled. My userform will be for use on other users computers which will not have these references enabled (and will be too troublesome to enable for all users).

My understanding is that the code I have is “early binding” and it needs to be “late binding” to bypass the need for other users to enable the reference to Word.

My problem is I do not know or understand how to do this with my code:

 Dim objWord As Word.Application
 Dim docWord As Word.Document

 Set objWord = CreateObject("Word.Application")
 Set docWord = objWord.Documents.Open(ThisWorkbook.Path & "\MyNewWordDocument.doc")

 docWord.ExportAsFixedFormat OutputFileName:=ThisWorkbook.Path & "\" + Cells(1, 6) + " - Offer Letter.pdf", _
 ExportFormat:=wdExportFormatPDF

 objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
 objWord.Quit

This code is fully functional for myself. However, as stated, it does not work for others due to the reference to Word not enabled.

Answers:

If the other users all have the same version of Office as you do, you don’t have a problem. If they have different versions, you do need to late bind, which only involves declaring the variables as Object and declaring any constants you use from Word’s library:

Dim objWord As Object
Dim docWord As Object
const wdExportFormatPDF as long = 17
const wdDoNotSaveChanges as long = 0

Set objWord = CreateObject("Word.Application")
Set docWord = objWord.Documents.Open(ThisWorkbook.Path & "\MyNewWordDocument.doc")

docWord.ExportAsFixedFormat OutputFileName:=ThisWorkbook.Path & "\" + _ Cells(1, 6) + " - Offer Letter.pdf", _
ExportFormat:=wdExportFormatPDF

objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
objWord.Quit

You should also remove the Word reference.

Might as well add a plug: http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/

Leave a Reply

Your email address will not be published. Required fields are marked *