[FAQ's: OD] How do I detect if an Office app is already running/created?

[RobDog888]

To determine if an Office app is already running or not we can just error trap its attachment error.

The reasons for reusing an app instance is for performance and optimizations of your app.

For example, if you wanted to add a new workbook to an already running instance of Excel you would just use GetObject to set your app object variable to it. But the issue is that you can not depend on it always already existing. So for that we just trap the error and if raised we can make a call to the CreateObject function to start a new instance of our desired Office app.

Also, by declaring our application object variable as "Object" we can support multiple versions of the Office app as we are using Late Binding.

You can substitute "Excel.Application" in the example with any Office .Application class type. Ie: Access, Word, Outlook, etc.


VB 6 And Excel 97-2007 Code Example:

Option Explicit
'Define our application object variable using Late Binding
Private oApp As Object
 
Private Sub Form_Load()
    'Designate our error handling procedure
    On Error GoTo MyError
    'Test the attachment of any possible already running instances our our desired Office app
    'If unsecussful then it will direct the code to execute the error handler - "MyError"
    Set oApp = GetObject(, "Excel.Application")
    'Test if it failed
    If TypeName(oApp) = "Nothing" Then
        'If it gets to here then we have been returned from the error handler
        'So we will want to create a new instance of the application object
        Set oApp = CreateObject("Excel.Application")
    End If
    Exit Sub
MyError:
    'Test for the error being "Cant create object"
    If Err.Number = 429 Then
        'It is so lets return to the next line of code to create a new instance of the app object
        'Its possible that this could be executed twice if the CreateObject line fails
        'In which case that would mean the Office app is not installed or such.
        Resume Next
    Else
        'It was another type of error so lets display it to the user
        MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
    End If
End Sub