How to safely instantiate another Office application and close it only if you started it

Author:

Tushar Mehta MVP

COMMUNITY SOLUTIONS CONTENT DISCLAIMER

MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.

Tips

Author
The author of this tip is Tushar Mehta.  You can reach him through his web site:
 
Introduction
Over time, it has become increasingly easier to use VBA code in one Office program to automate another similar program.  For example, a single statement such as
Set xlApp =new Excel.Application
is all that is needed to run Excel.  However, for all its ease, there are some unpleasant side-effects that could hurt the unwary developer.  This tip addresses two such issues.
 
However, before getting to the issue of side-effects, there is the question of early binding vs. late binding.  The above statement mandates the use of early binding, and in some cases that may not be so desirable.  This tip addresses not only the unpleasant side-effects but also works with both early and late binding.
 
Once a program is started (instantiated with the above Set statement) it is the developer's responsibility to manage it.  For applications (such as Excel) that can have multiple concurrent copies running, each execution of a statement like the one above starts up a new copy!  Hence, it is the developer's responsibility to terminate each executing copy with a statement like
xlApp.Quit
 
Otherwise memory will be littered with orphaned copies of the program that can only be terminated through the Windows Task Manager (activated with CTRL+ALT+DELETE).
 
On the other hand, for an application (such as PowerPoint) that can have only one copy running, a statement similar to the above Set statement will connect to the already running copy, if one is present.  A subsequent .Quit would be most annoying to a user who started the program and is using it to do some work!
 
A solution addressing both problems
Unlike the Set statement used above, it is possible to distinguish between connecting to an already running copy and connecting to a newly started copy of the program with the use of the GetObject() and CreateObject() VB functions.  Being able to distinguish between the two conditions means it is also possible to decide whether or not to terminate the program.
 
In the code below, which uses early binding, the GetObject() function, wrapped inside an error handler will connect up with an already running copy of the application (PowerPoint in this case).  If the variable ppApp is still nothing after the GetObject() function, clearly there is no copy of PowerPoint already running.  So, the CreateObject() function starts up the application and get a reference to this just-started copy.  In this case, the code also sets the flag IStartedPP.
 
Once the developer is done with whatever work needs to be done, the IStartedPP flag is a reminder of whether this code started the other application or not.  If it did, the other application is told to quit.  Finally, and unconditionally, the ppApp variable is finally set to Nothing.
 
Option Explicit
Sub StartOtherApp()
    Dim ppApp As PowerPoint.Application, IStartedPP As Boolean
    On Error Resume Next
    Set ppApp = GetObject(, "powerpoint.application")
    On Error GoTo 0
    If ppApp Is Nothing Then
        IStartedPP = True
        Set ppApp = CreateObject("powerpoint.application")
        End If
    '…
    'Do whatever stuff
    '…
    If IStartedPP Then
        ppApp.Quit
        End If
    Set ppApp = Nothing
    End Sub
 
Final Note
The code in this tip addresses the question of properly cleaning up after oneself when connecting with other applications, some of which may already be running and others may be initiated by the code.  In addition, the code works with both early-bound and late-bound clients.
 
References:
An introduction to early binding and late binding:
Early and Late Binding

Automating one office application from another:
Creation of Object Variables to Automate Another Office Application

Why an application doesn't quit even when it is told to go away:
Program won't quit

 
 
Proprietà

ID articolo: 555191 - Ultima revisione: 03 set 2004 - Revisione: 1

Microsoft Visual Basic for Applications 6.0

Feedback