Excelguru Help Site - 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

Knowledge Base Articles By Category

Examples Of Using Late Binding To Connect To Other Applications

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