Getting the window handle of all the open workbooks

[Jaafar Tribak]

Dear experts,

I am trying to get the caption that is displayed on each window of all the open workbooks. I am using the code below to loop through all the workbook windows, get their handles and finally display their respective captions on a Msgbox.

I am having 2 problems:

1- The code loops only once instead of once for each open workbook !
2- The Msgbox displays an empty string instead of the workbook caption !

If I am not mistaken, problem 2 is due to the fact that the Class name"EXCEL7" used in the code refers to a worksheet and not to a workbook. The trouble is I can't figure out what a Workbook Class name is so that I can use it in the FindWindowEx Function


Code:


<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> FindWindowEx <SPAN style="color:#00007F">Lib</SPAN> "user32" Alias "FindWindowExA" _
(<SPAN style="color:#00007F">ByVal</SPAN> hWnd1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> hWnd2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> lpsz1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> lpsz2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Declare</SPAN> <SPAN style="color:#00007F">Function</SPAN> GetWindowText <SPAN style="color:#00007F">Lib</SPAN> "user32" Alias "GetWindowTextA" _
(<SPAN style="color:#00007F">ByVal</SPAN> hwnd <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> lpString <SPAN style="color:#00007F">As</SPAN> String, <SPAN style="color:#00007F">ByVal</SPAN> cch <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>


<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
  <SPAN style="color:#00007F">Dim</SPAN> BookNameLenghth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
  <SPAN style="color:#00007F">Dim</SPAN> strBuffer <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
  <SPAN style="color:#00007F">Dim</SPAN> bBufferSize <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
  <SPAN style="color:#00007F">Dim</SPAN> lParent <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
  <SPAN style="color:#00007F">Dim</SPAN> lChild <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

  lParent = Application.hwnd
  <SPAN style="color:#00007F">If</SPAN> lParent <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">Do</SPAN>
      lChild = FindWindowEx(lParent, lChild, "EXCEL7", vbNullString)
      bBufferSize = 255
      strBuffer = <SPAN style="color:#00007F">String</SPAN>(bBufferSize, Chr(0))
      BookNameLenghth = GetWindowText(lChild, strBuffer, bBufferSize)
      MsgBox Left(strBuffer, BookNameLenghth)
    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> lChild
  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Obviously I could have used something like the code below but I wanted to get the workbook captions via their window handles:


<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()
    <SPAN style="color:#00007F">Dim</SPAN> W <SPAN style="color:#00007F">As</SPAN> Workbook
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> W <SPAN style="color:#00007F">In</SPAN> Application.Workbooks
        MsgBox W.Windows(1).Caption
    <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Any help will be much appreciated.

Regards.

 

[mark007]

Glad I could help.

 

[MrBuckeye]

Jaafar and mark007,

I, too, have been looking for this functionality. My objective is to get the button in the taskbar to flash when an Excel spreadsheet needs attention from the user. I had the code to make it flash assuming I had the handle, but was having difficulty finding code to get the handle. The code from Jaafar works most of the time. The problem that I have is that the MS-SDIa object doesn't get created unless I have to Excel workbooks open. If I only have one workbook, plus other applications, I don't find the handle. Any advice?

Also, mark007, your link to your website didn't work.

MrBuckeye

 

[John_w]

MrBuckeye - post your code.

 

[MrBuckeye]

This is the part of the code that I use to flash the TaskBar button. I test for the event that I am looking for, and when it occurs, I call the FindWorkbookHandle sub. It works fairly well, but only when I've opened at least a second workbook.


Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Type FLASHWINFO
cbSize As Long
hwnd As Long
dwFlags As Long
uCount As Long
dwTimeout As Long
End Type
Private Const FLASHW_TRAY = 2

Private Declare Function LoadLibrary Lib "kernel32" _
Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long
Private Declare Function GetProcAddress Lib "kernel32" _
(ByVal hModule As Long, ByVal lpProcName As String) As Long

Private Declare Function FreeLibrary Lib "kernel32" _
(ByVal hLibModule As Long) As Long
Private Declare Function FlashWindowEx Lib "user32" _
(FWInfo As FLASHWINFO) As Boolean

Public Sub FindWorkbookHandle() 'CommandButton1_Click()
Dim lBookNameLenghth As Long
Dim strBookName As String
Dim strBuffer As String
Dim lChild As Long
Const lBufferSize As Byte = 255
Const Msg1 As String = "This is the Caption Of workbook :**"
Const Msg2 As String = "Obtained by Using the FindWindowEx function "
strBuffer = String(lBufferSize, Chr(0))
Do
Counter = Counter + 1
lChild = FindWindowEx(0, lChild, "MS-SDIa", vbNullString)
If lChild = False Then Exit Do
lBookNameLenghth = GetWindowText(lChild, strBuffer, lBufferSize)
strBookName = Left(strBuffer, lBookNameLenghth)
If strBookName = "YahooQuery3" Then
FlashWindow (lChild)
Exit Do
End If
Loop While lChild
End Sub
Public Sub FlashWindow(hwnd As Long, _
Optional NumberOfFlashes As Integer = 5)
'***************************************************
'Purpose: Flashes a Window in the taskbar in order to notify
'a user of an event within a program
'Parameters: Hwnd=hwnd of frm to flash
'NumberofFlashes = Number of times to
'flash
'Notes: WINDOWS 98 OR 2000 is REQUIRED
'Uses FlashWindowEx API, which substitutes
'for bringing you window to the foreground
'obtrusively (e.g., on startup or when siginficant
'event occurs in your program) Windows 98/2000 no
'longer permits this
'Example:
'FlashWindow me.hwnd
'***************************************************
'Prevent Errors by checking if
'the API function is available on the
'Current OS
If Not APIFunctionPresent("FlashWindowEx", "user32") _
Then Exit Sub
Dim bRet As Boolean
Dim udtFWInfo As FLASHWINFO
With udtFWInfo
.cbSize = 20
.hwnd = hwnd
.dwFlags = FLASHW_TRAY
.uCount = NumberOfFlashes 'flash window 5 times
.dwTimeout = 0
End With
bRet = FlashWindowEx(udtFWInfo)
End Sub
Private Function APIFunctionPresent(ByVal FunctionName _
As String, ByVal DllName As String) As Boolean
'USAGE:
'Dim bAvail as boolean
'bAvail = APIFunctionPresent("GetDiskFreeSpaceExA", "kernel32")
Dim lHandle As Long
Dim lAddr As Long
lHandle = LoadLibrary(DllName)
If lHandle <> 0 Then
lAddr = GetProcAddress(lHandle, FunctionName)
FreeLibrary lHandle
End If

APIFunctionPresent = (lAddr <> 0)
End Function

 

[John_w]

It works for me with:

lChild = FindWindowEx(0, lChild, "XLMAIN", vbNullString)

instead of:

lChild = FindWindowEx(0, lChild, "MS-SDIa", vbNullString)

 

[pbornemeier]

Update to link shown in post #8 is The Code Net