Excelguru Help Site - Connecting To/Creating A New Application Instance Via Late Binding

Introduction:
There are actually two different ways to accomplish this task, depending on exactly what you want to do. You can:

Connect to an existing application (uses the GetObject method)
Create a new application with no document loaded (uses the CreateObject method)

Please note that while all of this code was written to control Microsoft Word from Excel, you can use it in any other VBA enabled application, and you can also use it to control any other VBA enabled application, simply by changing "Word" to Excel, Powerpoint, Access, etc... in the examples below.
For ready built examples, click here!

Connecting to an existing instance of an application:
To connect to an existing application, we will use the GetObject method. It is important to note that this method will always return the first instance of the program. It is therefore advisable to have only one open instance of the application being sought after, to avoid unexpected results.

To demonstrate the following code, make sure that you have an instance of Word open on your system, preferably with a blank document in it. Copy the following code into a standard module in Excel and run it. The text "I was created by a macro!" should appear in the Word document.

Code:

Sub BindToWord()
'Author       : Ken Puls (www.excelguru.ca)
'Macro purpose: To bind to the open instance of Word

Dim objApp As Object

'Turn on error handling and bind to the application
    On Error GoTo ErrHandler
    Set objApp = GetObject(, "Word.Application")

'Your code goes here
    objApp.Selection = "I was created by a macro!"

ErrHandler:
'Release the object and resume normal error handling
    Set objApp = Nothing
    On Error GoTo 0
End Sub

Creating a new instance of an application:
To create a new instance of an application, we will use the CreateObject method. Unlike GetObject, this method binds to the object it creates, so there is no need to worry about if another instance of the program already exists. Having said that, if you will need to bind to a created object later, keep in mind that if you create a new instance, there may be two open, which will defy your GetObject code later.

To demonstrate the following code, copy the following code into a standard module in Excel and run it. Word should appear with a new (blank) document ready.

Code:

Sub CreateNewWord()
'Author       : Ken Puls (www.excelguru.ca)

Dim objApp As Object

'Turn on error handling
    On Error GoTo ErrHandler

'Create the application
    Set objApp = CreateObject("Word.Application")
    With objApp
        .Visible = True
        .Documents.Add
    End With

'Your code goes here

ErrHandler:
'Release the object and resume normal error handling
    Set objApp = Nothing
    On Error GoTo 0
End Sub

Creating a new instance of an application only if necessary:
The following code uses both methods to return the active instance of Word, if it exists, and create a new instance if it doesn't.

Code:

Sub AttachToWord()
'Author       : Ken Puls (www.excelguru.ca)

Dim objApp AsObject

'Attempt to bind to an open instance
    On Error Resume Next
    Set objApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
        'Could not get instance, so create a new one
        Err.Clear
        On Error GoTo ErrHandler
        Set objApp = CreateObject("Word.Application")
        With objApp
            .Visible = True
            .Documents.Add
        End With
    Else
        'Bound to instance, activate error handling
        On Error GoTo ErrHandler
    End If

'Your code goes here

ErrHandler:
'Release the object and resume normal error handling
    Set objApp = Nothing
    On Error GoTo 0
End Sub

I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!