Visual Basic 6 checking Excel instance running

Question

I want to check whether any excel instance is running at the time of opening my program. The following code is used.

Const ERR_APP_NOTRUNNING As Long = 429
On Error Resume Next
Set xlApp = GetObject("Excel.Application")
If Err = ERR_APP_NOTRUNNING Then
Set xlApp = Nothing
Exit Sub
Else:
Set xlApp = Nothing
MsgBox ("Sorry, please restart after closing all Excel files.")
End
End If

This code works fine in Office 2007. But its not working in Office 2010. Can someone help me out so that it may work on all office versions to date?

Answer 1

Here's a Win32 API way of checking for a running process. Copy the following code into a module:

Option Explicit
DefLng A-Z

Private Const TH32CS_SNAPPROCESS As Long = &H2

Private Type PROCESSENTRY32
   dwSize As Long
   cntUsage As Long
   th32ProcessID As Long
   th32DefaultHeapID As Long
   th32ModuleID As Long
   cntThreads As Long
   th32ParentProcessID As Long
   pcPriClassBase As Long
   dwFlags As Long
   szExeFile As String * 260
End Type

Private Declare Function CreateToolhelp32Snapshot Lib "kernel32" (ByVal dwFlags As Long, _
   ByVal th32ProcessID As Long) As Long
Private Declare Function Process32First Lib "kernel32" (ByVal hSnapshot As Long, _
   lppe As PROCESSENTRY32) As Long
Private Declare Function Process32Next Lib "kernel32" (ByVal hSnapshot As Long, _
   lppe As PROCESSENTRY32) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long

Public Function IsProcessRunning(ByVal sProcessEXE As String) As Boolean

   Dim lProcessSnapshot As Long
   Dim udtProcess As PROCESSENTRY32
   Dim bolExists As Boolean
   
   If LenB(sProcessEXE) <> 0 Then
      lProcessSnapshot = CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS, 0&)
      udtProcess.dwSize = LenB(udtProcess)
      
      If lProcessSnapshot > 0 Then
         If Process32First(lProcessSnapshot, udtProcess) <> 0 Then
            Do
               If InStr(1, Trim0(udtProcess.szExeFile), sProcessEXE, vbTextCompare) > 0 Then
                  bolExists = True
                  Exit Do
               End If
            Loop Until Process32Next(lProcessSnapshot, udtProcess) = 0
         End If
         
         'Close snapshot handle
         CloseHandle lProcessSnapshot
      End If
      
      'Return information
      IsProcessRunning = bolExists
   End If
   
End Function

Private Function Trim0(ByVal sText As String) As String
   Trim0 = Trim$(Replace$(sText, Chr$(0), vbNullString))
End Function

Call it like

Debug.Print IsProcessRunning("excel.exe")