• Examples Of Using Late Binding To Connect To Other Applications

    Introduction:
    This article contains examples of using late binding to attach to the current instance or create a new instance of a variety of applications. It contains examples for creating the following from another application:
    • Access
    • Excel
    • Powerpoint
    • Publisher
    • Word

    Please note that you can copy these code examples into any application that supports VBA, but the called application must be installed on your system for it to work. Just copy them into a standard module and run them. (No references need to be set in the Visual Basic Editor in order to make this code work.)

    Access:
    This code will bind to an existing instance of Access, or create a new instance and bind to it. Note that it will inform you that it has successfully bound to an instance of the application, but it will not do anything else. This is by design, as I do not feel that it is adviseable to run some of my own code which could alter your database in any way.
    Code:
    Sub GetAccess()
    'Author       : Ken Puls (www.excelguru.ca)
    
    'Bind to an existing or created instance of Microsoft Access
    Dim objApp As Object
    On Error Resume Next
    Set objApp = GetObject(, "Access.Application")
    
    If Err.Number <> 0 Then
        'Could not get instance, so create a new one
        Err.Clear
        On Error GoTo ErrHandler
        Set objApp = CreateObject("Access.Application")
        With objApp
            .Visible = True
        End With
    Else
        On Error GoTo ErrHandler
    End If
    
    'Inform the user of success
    MsgBox "I have successfully bound to an Access application!"
    
    ErrHandler:
    'Release the object and resume normal error handling
    Set objApp = Nothing
    On Error GoTo 0
    End Sub
    Excel:
    This code will bind to an existing instance of Excel, or create a new instance and bind to it. It will then add a new worksheet and place text in cell A1 of that worksheet.
    Code:
    Sub GetExcel()
    'Author       : Ken Puls (www.excelguru.ca)
    
    'Bind to an existing or created instance of Microsoft Excel
    Dim objApp As Object
    
    'Attempt to bind to an open instance
    On Error Resume Next
    Set objApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        'Could not get instance, so create a new one
        Err.Clear
        On Error GoTo ErrHandler
        Set objApp = CreateObject("Excel.Application")
        With objApp
            .Visible = True
            .Workbooks.Add
        End With
    Else
        'Bound to instance, activate error handling
        On Error GoTo ErrHandler
    End If
    
    'Add some text to the document
    With objApp.ActiveWorkbook
        .Worksheets.Add
        .Worksheets(1).Range("A1") = "Hello!"
    End With
    
    ErrHandler:
    'Release the object and resume normal error handling
    Set objApp = Nothing
    On Error GoTo 0
    End Sub
    Powerpoint:
    This code will bind to an existing instance of Powerpoint, or create a new instance and bind to it. It will then insert a new slide and place some text in the title.
    Code:
    Sub GetPowerpoint()
    'Author       : Ken Puls (www.excelguru.ca)
    
    'Bind to an existing or created instance of Microsoft Powerpoint
    Dim objApp AsObject
    
    'Attempt to bind to an open instance
    On Error Resume Next
    Set objApp = GetObject(, "Powerpoint.Application")
    If Err.Number <> 0 Then
        'Could not get instance, so create a new one
        Err.Clear
        On Error GoTo ErrHandler
        Set objApp = CreateObject("Powerpoint.Application")
        With objApp
            .Visible = True
            .Presentations.Add
        End With
    Else
        'Bound to instance, activate error handling
        On Error GoTo ErrHandler
    End If
    
    'Add some text to the document
    With objApp
        .ActiveWindow.View.GotoSlide _
            Index:=objApp.ActivePresentation.Slides.Add(Index:=1, _
            Layout:=1).SlideIndex
        .ActivePresentation.Slides(1).Shapes.Title.TextFrame.Textrange.Text _
            = "Hello!"
    End With
    
    ErrHandler:
    'Release the object and resume normal error handling
    Set objApp = Nothing
    On Error GoTo 0
    End Sub
    Publisher:
    This code will bind to an existing instance of Powerpoint, or create a new instance and bind to it. It will then insert a new slide and place some text in the title.
    Code:
    Sub GetPublisher()
    'Author       : Ken Puls (www.excelguru.ca)
    
    'Bind to an existing or created instance of Microsoft Publisher
    Dim objApp As Object, oShp As Object
    
    'Attempt to bind to an open instance
    On Error Resume Next
    Set objApp = GetObject(, "Publisher.Application")
    If Err.Number <> 0 Then
        'Could not get instance, so create a new one
        Err.Clear
        On Error GoTo ErrHandler
        Set objApp = CreateObject("Publisher.Application")
        With objApp
            .ActiveWindow.Visible = True
        End With
    Else
        On Error GoTo ErrHandler
    End If
    
    'Add picture and text to the document
    With objApp.ActiveDocument.Pages(1)
        Set oShp = .Shapes.AddShape(Type:=93, _
            Left:=144, Top:=144, Width:=72, Height:=144)
        With oShp
            .TextFrame.TextRange.Text = "Hi there!"
        End With
    End With
    
    ErrHandler:
    'Release the objects and resume normal error handling
    Set oShp = Nothing
    Set objApp = Nothing
    On Error GoTo 0
    End Sub
    Word:
    This code will bind to an existing instance of Word, or create a new instance and bind to it. Once bound, it places "Hello!" as the first paragraph of the document:
    Code:
    Sub GetWord()
    'Author       : Ken Puls (www.excelguru.ca)
    
    'Bind to an existing or created instance of Microsoft Word
    Dim objApp As Object
    
    '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
    
    'Add some text to the document
    With objApp.Documents(1)
        .Paragraphs(1).Range.InsertParagraphBefore
        .Paragraphs(1).Range.Text = "Hello!" & vbNewLine
    End With
    
    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

    VBA Programming help

    Code:
    Public Sub KeepHighest()
    Dim lastrow As Long
    Dim i As Long
        
        Application.ScreenUpdating = False
        With ActiveSheet
    ...

    Bob Phillips Today, 10:56 AM Go to last post
    Shoeboxken

    VBA Programming help

    I would like to use a VBA coding to create a list of information pulled from a data set.

    Data would include the following info as examples:...

    Shoeboxken Today, 05:36 AM Go to last post
    facams

    delete duplicate entries

    Thank you sir will try it...

    facams Yesterday, 09:00 PM Go to last post
    Bob Phillips

    delete duplicate entries

    Code:
    Public Sub DeleteDuplicates()
    Dim lastrow As Long
    Dim i As Long
        Application.ScreenUpdating = False
        
        With ActiveSheet
    ...

    Bob Phillips Yesterday, 08:25 PM Go to last post
    facams

    delete duplicate entries

    HI, new here. I need help in deleting duplicate cells that have same name or account # then based on an action that states "report" even if...

    facams Yesterday, 07:57 PM Go to last post