How To: Late Bind

UPDATED June 26, 2013

Because of some discoveries that came with experience, this post has been modified and re-published.  In essence, the discovery is that there were some untested assumptions about the consistency of CreateObject() and GetObject() for all applications. Therefore, this post is being revisited and revised.

The first revision is this clarification based on my discovery.  While I believe that most "foreign" applications can be bound into a VBA project, this focuses mainly on some Office Applications.  That does not mean many of the principles don't apply elsewhere, only that the research to determine any idiosyncrasies would keep me from ever completing this post.

• Excel, PowerPoint, Publisher, Project and Word are most consistent in their behavior and can have multiple files open per instance.
• Access, perhaps because it is a single-file per instance model, does not follow the same meta-model as do the above.
• Outlook does not seem to have a collection of files that can be treated as such.
• I have no idea how to deal with SharePoint, InfoPath, OneNote, Visio, Groove, nor any non-Microsoft application.  For OneNote, I've seen one difference discussed: It is more XML-based.

At some point I intend on writing a series of posts regarding working with Late Bound instances of the Office products included in my projects, and maybe a something on a non-Microsoft application if that opportunity arises.  I have in the works two other posts on last binding.

Introduction

A number of posts in this blog mention late binding.  Binding: Accessing Other Office Apps summarizes provides a description/definition of "binding", discusses the two types of binding (Early and Late), and contrasts the two.  The purpose of this post is to give a succinct description of how to Late Bind.

In short, "binding" is the act of including a "foreign" object library in a "host", conceptually related to #include.  Late binding is initiated during compile/translation.  Early binding is created during before compile/translation.

How to Late Bind

In Early Binding, there are a four non-coding steps taken to perform the binding.  In late binding, all is done within the Subs and Functions.

Because there is no reference to the foreign Object Library, all foreign objects declared as generic objects.

The first object that needs to be declared is one for the foreign application and its object library.  As a naming convention in this post I use "app" to identify the purpose of these objects.

• Dim appAccess as Object
• Dim appExcel as Object
• Dim appOutlook as Object\
• Dim appPowerPoint  as Object
• Dim appProject as Object
• Dim appPublisher as Object
• Dim appWord as Object

Likewise, subordinate objects need to be declared as objects and the naming conventions should provide a clue as to how they're to be used.

• Dim colPresentations as Object ' The Collection of Presentations to be found in appPowerPoint,
• Dim oPres as Object ' A PowerPoint Presentation to be found in colPresentations 
• Dim colWordDocs  as Object ' The Collection of documents to be found in appWord,
• Dim colPresDocs  as Object ' The Collection of documents to be found in appPublisher,
• Dim colWorkBooks as Object ' The Collection of WorkBooks to be found in appExcel,
• Dim wkbk  as Object ' The generic  WorkBook to be found in colWorkBooks,
• Dim colWorkSheets as Object ' The Collection of WorkSheets to be found in wkbk,
• Dim docAgreement As Object ' A specific Document in colWordDocs

NOTE:  This is not an complete list of the top-level collections and files.

Before accessing any of the subordinate objects to be found in an application's Object Library, the application object, e.g. appWord above, must be instantiated.

• To create a new instance (session) use Set <variable> = CreateObject()
• Set appAccess = CreateObject("Access.Application") ''Creates a new instance of Access
• Set appExcel = CreateObject("Excel.Application") 'Creates a new instance of Excel
• Set appOneNote = CreateObject("OneNote.Application") 'Creates a new instance of OneNote if OneNote is running. 
   NOTE:   By experimentation I've come to the conclusion that when OneNote is already open, CreateObject("OneNote.Application") does not create a new instance of OneNote but behaves more like like one would expect GetObject(,"OneNote.Application") to behave.  GetObject(,"OneNote.Application") fails. (see below)
• Set appOutlook = CreateObject("Outlook.Application") 'Creates a new instance of Outlook
   NOTE 1:   By experimentation I've come to the conclusion that when Outlook is already running, CreateObject("Outlook.Application") does not create a new instance of Outlook but behaves more like like GetObject(,"Outlook.Application") (see below).
   NOTE 2:   If Outlook is not running, more than one Profile is available, and no Profile is identified as the one to always use this results in the Choose Profile Dialogue being presented.
• Set appPowerPoint = CreateObject("PowerPoint.Application") ''Creates a new instance of PowerPoint
• Set appPowerPoint = CreateObject("MSProject.Application") ''Creates a new instance of Project
• Set appPublisher = CreateObject("Publisher.Application") ''Creates a new instance of Publisher
• Set appWord = CreateObject("Word.Application") 'Creates a new instance of Word

• To use an existing instance (session) use Set <variable> = GetObject()
  NOTE 1:  The following will generate an automation error if no instance of the application is active.


• Set appAccess = GetObject(,"Access.Application") 'Uses an existing instance of Access
• Set appExcel = GetObject(,"Excel.Application")   'Uses an existing instance of Excel
• Set appPowerPoint = GetObject(,"PowerPoint.Application")  'Uses an existing instance of PowerPoint
• Set appProject = GetObject(,"MSProject.Application")  'Uses an existing instance of Project
• Set appPublisher = GetObject(,"Publisher.Application")  'Uses an existing instance of Publisher
• Set appOneNote = GetObject(,"OneNote.Application")  ' Fails
  
• Set appOutlook = GetObject(,"Outlook.Application")  'Uses an existing instance of Outlook
• Set appWord = GetObject(,"Word.Application")  'Uses an existing instance of Word

At this point the instantiated objects have all of the events, properties and methods as specified in the bound "foreign" Object Library.  For example, in the bound instance on Word, a new Document can created  

• Set wkbkAnalysis  = appExcel Workbooks.Add ' Adds a new Workbook to the appExcel.Workbooks collection
• Set colWorkBooks = appExcel Workbooks 'Instantiates colWorkBooks as the Workbooks collection of appExcel
• Set wkbkAnalysis  = colWorkBooks .Add  ' Adds a new Workbook to the appExcel.Workbooks collection.
                                                                        ' NOTE: Does not work if colWorkBooks was not first instantiated.
• Set presMonthlyReport  = appPowerPoint.Presentations.Add  ' Adds a new Document to the appWord.Documents collection
• Set projNewPlan  = appProject.Projects.Add  ' Adds a new Project to the appProject .Projects collection
• Set docAgreement  = appPublisher.Documents.Add  ' Adds a new Document to the appPublisher.Documents collection
• Set docAgreement  = appWord.Documents.Add  ' Adds a new Document to the appWord.Documents collection

The above technique cannot be applied to Access, OneNote, or Outlook because they do not have a top-level collection.

• Access is a single-file application.  Each instance of Access can have only one Access database open at a time.
• OneNote uses a COM API, possibly because it is written with an XML-base.
• Outlook is not file-oriented.