• 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!

  • MVP Logo
  • Recent Forum Posts

    Bob Phillips

    Index/match with multiple match criteria

    When I said Q1, I meant Quarter 1, cell E31, not cell Q1.

    It most definitely does match your last upload, C8:C30 is the date which I parse,...

    Bob Phillips Yesterday, 06:44 PM Go to last post
    GLewis14

    Consolidate paired data sets

    hi all,

    I have a sheet which contains data sets (3 rows per data set), see Product Level sheet of attachment. I want to consolidate and total...

    GLewis14 Yesterday, 02:46 PM Go to last post
    alansidman

    Please help!

    What is the criteria for counting the quantity. Would be helpful if you provided a sample workbook showing what you have and what your expected results...

    alansidman Yesterday, 06:21 AM Go to last post
    cprop76

    Please help!

    I am trying to count two separate pieces of data which appear in the same excel column. The columns will have a date followed by a quantity so look something...

    cprop76 Yesterday, 05:52 AM Go to last post
    anon125

    does excel 2002 have page layout>themes and colors

    using a great vertex spreadsheet, but to change the colours it says to use page layout>themes and page layout>colors
    I assume my old version...

    anon125 Yesterday, 01:38 AM Go to last post