In Office – Applications – Started!, Ralf Stolzenburg posted sample VBA code for instantiating several Office applications. I've written about this subject as well, in Easy Application.Object References.
I like my code better, because it avoids the global Object variables present in Ralf's code. (Although as we'll see later, at least one global Boolean variable may be required.) I'd prefer to have one routine create (and return) the object, then pass it to another routine that does something else. It's all part of the encapsulation and loose coupling attitude I've mentioned elsewhere.
The other thing that could be improved is the logic behind the actual instantiation. GetObject is used to start every application, then CreateObject is used if GetObject fails.
The problem is that, as a developer, you may not always want to grab an existing instance of a running application. In fact, I would even go so far as to say that you shouldn't grab an existing instance of a running application, unless unavoidable. Proper code should never disturb the end user's currently running applications, and should not require a running instance of a needed application in order to operate. (I'd be glad to hear otherwise.)
As we know, Outlook and PowerPoint are the two Office apps that only allow one running instance. Otherwise, for applications like Word, Access and Excel, we should be creating our own instances, then shutting them down when our code is finished. Outlook and PowerPoint should be checked to see if they are already running — if yes, grab the running instance; if no, create an instance and shut it down when our code is complete.
This is the purpose of the GetApp function in Easy Application.Object References, but it does need some updating. The new and improved GetApp function follows.
Dim bWeStartedApp As Boolean
Function GetApp(AppName As String) As Object
' AppName can be one of the following:
' Word, Outlook, Excel, PowerPoint, InternetExplorer (no space), Access, Shell
' or any other application that can be created using "AppName.Application"
' if any error occurs, just continue
On Error Resume Next
' basic error check
If AppName = "" Then Exit Function
' if the application reference being sought is actually the current application,
' set a reference to the native application object
If InStr(UCase$(Application.Name), UCase$(AppName)) > 0 Then
Set GetApp = Application
Exit Function
End If
If (UCase$(AppName) = "OUTLOOK") Or (UCase$(AppName) = "POWERPOINT") Then
' PPT and OL can only have one instance running, try to hook it before
' creating new process
Set GetApp = GetObject(, AppName & ".Application")
If Err.Number <> 0 Then
Set GetApp = CreateObject(AppName & ".Application")
bWeStartedApp = True
End If
Exit Function
Else
' Word, Excel, Powerpoint, InternetExplorer, etc
Set GetApp = CreateObject(AppName & ".Application")
bWeStartedApp = True
End If
End Function
The Boolean variable above the function must be placed at the top of a standard module (the same one where this function is placed). The ReleaseApp function would then be called as follows:
Sub test() Dim App As Object ' get internet explorer reference Set App = GetApp("Outlook") ' App will equal Nothing if an error occurred in the function If App Is Nothing Then Exit Sub ' release it and close instance ' bWeStartedApp will be true if we started the application, otherwise ' we don't want to quit If ReleaseApp(App, bWeStartedApp) Then MsgBox "All gone!" End If End Sub
The global Boolean variable bWeStartedApp will only be True if we started any of our apps using CreateObject. Note that CreateObject, for classes that only allow one running instance, will try to hook an existing instance before attempting to create a new one. Therefore we need to use GetObject first for those applications.
If GetObject fails, we know the application wasn't already running (i.e. our process started it), so we can safely close it without disturbing the user.
About JP
1 Comment:
Sometimes you DO want the existing instance, such as when you are exporting your pretty Excel charts into a PowerPoint presentation.
You can pass an optional argument to the GetApp function.
Function GetApp(AppName As String, bExistingOnly As Boolean) As Object
Or you might replace the Boolean with an enumeration:
Public Enum appInstanceType
appExistingOnly = 1
appNewOnly = 2
appWhateverIsAvailable = 3
End Enum