Determine instances of Excel open

[Les]

Hi all, I am would like to know if it is possible to detemine how many
instances of Excel are open. I know it is possible to check how many
workbooks, with the code below but i wouold like to check the number of
instances open, before closing the application.

Sub chkWkbToClosePo()
'
Dim wkb As Integer
Application.IgnoreRemoteRequests = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
wkb = Application.Workbooks.Count
If wkb <= 2 Then
Application.Quit
Else
ActiveWorkbook.Close
End If

End Sub

 

[Rick S.]

Funny, I am trying to force a second instance but found how to count
instances open. The authors name is Jennifer
'======
Sub ExcelInstances() 'Office discussion Groups author: Jennifer
Dim XLCount
Dim oWMI

Set oWMI = GetObject("winmgmts:")
XLCount = 0

For Each Process In oWMI.InstancesOf("Win32_Process")
If UCase(Process.Name) = "EXCEL.EXE" Then
XLCount = XLCount + 1
End If
Next

MsgBox "Excel Instances Open: " & XLCount

Set oWMI = Nothing

End Sub
'======

 

[Peter T]

I see your setup from your signature, but others thinking of distributing
this should be aware that "winmgmts" is not installed in early OS, eg w9x,
by default. Following should work in all versions and I suspect faster than
creating an instance of "winmgmts"

Option Explicit

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Function GetWindow Lib "user32" ( _
ByVal hWnd As Long, ByVal wCmd As Long) As Long

Private Declare Function GetClassName Lib "user32" Alias _
"GetClassNameA" ( _
ByVal hWnd As Long, ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long

'Private Declare Function GetWindowText Lib "user32" Alias _
' "GetWindowTextA" ( _
' ByVal hWnd As Long, ByVal lpString As String, _
' ByVal cch As Long) As Long
'Public Declare Function GetDesktopWindow Lib "user32" () As Long

Private Const GW_HWNDFIRST = 0
'private Const GW_HWNDLAST = 1
Private Const GW_HWNDNEXT = 2
'private Const GW_HWNDPREV = 3
'private Const GW_OWNER = 4
'Private Const GW_CHILD = 5
'private Const GW_MAX = 5

Sub Test()
MsgBox ExcelCount
End Sub

Function ExcelCount() As Long
Dim hWin As Long
Dim nXLinsts As Long
Dim sBuff As String * 7
Const CXL As String = "XLMAIN"

hWin = FindWindow(CXL, vbNullString) ' normally incl app.caption

hWin = GetWindow(hWin, GW_HWNDFIRST)

Do
hWin = GetWindow(hWin, GW_HWNDNEXT)

Call GetClassName(hWin, sBuff, 7)

If Left$(UCase$(sBuff), 6) = CXL Then
nXLinsts = nXLinsts + 1
End If

Loop Until hWin = 0

ExcelCount = nXLinsts

End Function

Remove the commented declarations if not required for other purposes

Regards,
Peter T