Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

In the previous section, I discuss various ways to start an application. You might find that if an application is already running, using the Shell function could start another instance of it. In most cases, however, you want to activate the instance that's running - not start another instance of it.

Using AppActivate

The following StartCalculator procedure uses the AppActivate statement to activate an application if it's already running (in this case, the Windows Calculator). The argument for AppActivate is the caption of the application's title bar. If the AppActivate statement generates an error, it indicates that the Calculator is not running. Therefore, the routine starts the application.

Sub StartCalculator() Dim AppFile As String Dim CalcTaskID As Double AppFile = "Calc.exe" On Error Resume Next AppActivate "Calculator" If Err <> 0 Then Err = 0 CalcTaskID = Shell(AppFile, 1) If Err <> 0 Then MsgBox "Can't start Calculator" End If End Sub

Activating a Microsoft Office application

If the application that you want to start is one of several Microsoft applications, you can use the ActivateMicrosoftApp method of the Application object. For example, the following procedure starts Word:

Sub StartWord() Application.ActivateMicrosoftApp xlMicrosoftWord End Sub

If Word is already running when the preceding procedure is executed, it is activated. The other constants available for this method are:

• xlMicrosoftPowerPoint

• xlMicrosoftMail (activates Outlook)

• xlMicrosoftAccess

• xlMicrosoftFoxPro

• xlMicrosoftProject

• xlMicrosoftSchedulePlus