How To: Late Bind (2) - Existing or New Instance

Introduction

In How To: Late Bind, the ability to either bind to an existing instance of or a new instance of another "foreign" Office application, e.g. binding an Excel project to an instance of Word, was covered.

What was not included was the ability to deal with instances when it is not known if an instance of the foreign application is already active.  Binding to an existing instance saves memory, i.e. only one instance of Word (or Excel or...) takes less memory than opening a second (or third) instance.

First with the old, then with a new

This is for the cases when an instance of the foreign application may be running and it is preferable to attach to it because this requires less memory.  The counterargument for this approach is it may interfere with other uses of that occurrence, e.g. editing a Word document that is not part of the project.  The balancing argument may be whether the host application will be doing a large amount of background (batch) work in the foreign application.

The following function is specific to Excel.

Function fn_appExcel() As Object

    On Error Resume Next

    Set fn_appExcel = GetObject(, "Excel.Application")

    If Err.Number <> 0 Then

        Set fn_appExcel = CreateObject("Excel.Application")

    End If

    On Error GoTo 0

End Function
Other MS-Office applications can have a similar function, or the whole thing can be generalized by using a parameter for the application name.

Function fn_appOffice(appName as String) As Object

    On Error Resume Next

    Set fn_appOffice= GetObject(,  appName  & ".Application")

    If Err.Number <> 0 Then

        Set fn_appOffice= CreateObject(appName  & ".Application")

    End If

    On Error GoTo 0

End Function

That one ... or a new one

In this case, the need is to have an instance open with a specific file.  First, try to bind to the instance already running and has the correct open file.  If it cannot be found, open a new instance and the file that is needed

Function fn_appExcelWithWorkbook(Optional strDirAndFileName as string) As Object

    On Error Resume Next

    Set fn_appExcel = GetObject(strDirAndFileName , "Excel.Application")

    If Err.Number <> 0 Then

        Set fn_appExcel = CreateObject("Excel.Application")

        fn_appExcel .Workbooks.Open strDirAndFileName 

    End If

    On Error GoTo 0

End Function

REMINDER: This structure should be limited to binding Word, Excel, and PowerPoint as foreign applications.  Binding to other applications (e.g. Project, Outlook, and Access) will be covered in one more more subsequent posts.Check the Cross Application Development page of this blog.