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 exisiting 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 advisible 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

Knowledge Base Articles By Category

Connecting To/Creating A New Application Instance Via Late Binding

Rate this article

Donations

Recent Forum Posts

How to Rename a File from English to Other Language?

Link a series name to a cell using Excel 2010 VBA

How to Rename a File from English to Other Language?

Shared file\macro & IP address

Shared file\macro & IP address