Determine Number of Instances of Excel Running.

**bdb1974** · 10-31-2011, 04:26 PM

Hi all,

I've been stumbling around with this for awhile now. I was wanting some code that could
tell me if a workbook is open. However, I am wanting it to be able to check for the workbook within another instance of a excel app if running in a different thread.

I searched the internet for some exsisting code and could only find the following that looked promising.

http://www.eggheadcafe.com/microsoft...xcel-open.aspx

However, after inserting the code and making a few tweaks to the declarations.
I'm now stumbeling on the following line of code .

Call GetClassName(hWin, sBuff, 7)

sBuff, appears to be empty, I'm not sure what sBuff is suppose to hold.

Here's the full code I'm trying to use.

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" ( _
ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
Private Const GW_HWNDFIRST = 0
Private Const GW_HWNDNEXT = 2


Sub Test()
MsgBox ExcelCount
End Sub

Function ExcelCount() As Long
'http://www.eggheadcafe.com/microsoft/Excel-Programming/31869454/determine-instances-of-excel-open.aspx
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

Any help is appreciated.

Thanks,

BDB

**Kenneth Hobson** · 10-31-2011, 04:50 PM

The Alias for GetClassName was not set.

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 Const GW_HWNDFIRST = 0
Private Const GW_HWNDNEXT = 2

Function ExcelCount() As Long
  'http://www.eggheadcafe.com/microsoft/Excel-Programming/31869454/determine-instances-of-excel-open.aspx
  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)
  
  sBuff = Space(7)
  Do
    hWin = GetWindow(hWin, GW_HWNDNEXT)
    GetClassName hWin, sBuff, 7
    If Left$(UCase$(sBuff), 6) = CXL Then nXLinsts = nXLinsts + 1
  Loop Until hWin = 0
  
  ExcelCount = nXLinsts
End Function

Sub Test_ExcelCount()
  MsgBox ExcelCount
End Sub

**snb** · 10-31-2011, 05:05 PM

Probably simpler:

Sub snb()
 With CreateObject("Word.application")
  For Each ts In .tasks
   If InStr(ts, "Microsoft Excel") > 0 Then c01 = c01 & vbLf & ts
  Next
 End With

 MsgBox Mid(c01, 2), , "Number of Excel instances"
End Sub

**bdb1974** · 10-31-2011, 05:34 PM

Thanks for the response guys.

The Alias for GetClassName was not set.

Hoping you can give me a little more info instead of struggling anymore...
How do I set this and to what? Is it the Workbook name?

I'll give both methods a try. Right now , my day is done.

Thanks again,

BDB

**bdb1974** · 11-01-2011, 01:04 PM

Got SNB method to work for me.

I'm still curious on the other method for educational purposes.
Just not sure what needs to be done.

Tried setting sBuff = "test.xlsx"

and

GetClassNameA = "test.xlsx"

Neither works.

NE1 knows please help.
BDB

**Kenneth Hobson** · 11-01-2011, 02:21 PM

No, you don't send any filename. Just put my code into a Module and run Test_ExcelCount or as a UDF put =ExcelCount as a cell formula. As a UDF, it would update at each calculation.

I have used the other method on the link as well.

Of course this is just the first part of what you need, count instances of Excel.