Getting the window handle of all the open workbooks

Jaafar Tribak · Apr 10, 2004

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:

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 Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long

Private Sub CommandButton1_Click()
 Dim BookNameLenghth As Long
 Dim strBuffer As String
 Dim bBufferSize As Long
 Dim lParent As Long
 Dim lChild As Long

 lParent = Application.hwnd
 If lParent Then
 Do
 lChild = FindWindowEx(lParent, lChild, "EXCEL7", vbNullString)
 bBufferSize = 255
 strBuffer = String(bBufferSize, Chr(0))
 BookNameLenghth = GetWindowText(lChild, strBuffer, bBufferSize)
 MsgBox Left(strBuffer, BookNameLenghth)
 Loop While lChild
 End If
End Sub


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

Sub Test()
 Dim W As Workbook
 For Each W In Application.Workbooks
 MsgBox W.Windows(1).Caption
 Next
End Sub


Any help will be much appreciated.

Regards.

Jaafar Tribak · Apr 10, 2004

With the help of WinSpy++, I have been able to find the Class name (I run Excel Version :10.0) of a Workbook which happens to be called : MS-SDIa . However the code above still doesn't work when I replace EXCEL7 with MS-SDIa

I tested the "MS-SDIa" Class name with the code below which displays the active workbook's window caption:

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 GetParent Lib "user32" (ByVal hwnd As Long) As Long

Sub WkbkHndle_Test()
 Dim BookNameLenghth As Long
 Dim strBuffer As String
 Dim bBufferSize As Long
 Dim wkbkHwnd As Long

 wkbkHwnd = FindWindow("MS-SDIa", vbNullString)
 bBufferSize = 255
 strBuffer = String(bBufferSize, Chr(0))
 BookNameLenghth = GetWindowText(wkbkHwnd, strBuffer, bBufferSize)
 MsgBox Left(strBuffer, BookNameLenghth)
End Sub


If only I could find the parent of the workbook window using the GetParent API Function, I believe I could use the FindWindowEx in a loop to return all the open workbook handles .Unfortunately I have tried that but the GetParent function fails and retuns 0.

Any Ideas out there ?

Regards.

Jaafar Tribak · Apr 10, 2004

Bupm!

sbendbuckeye · Apr 10, 2004

Hello,

Windows API is pretty much out of my area of expertise, but I think you want to use EnumChildWindow or something like that.

For the current application, does this help?

Sub Test()
Dim win As Window
For Each win In Application.Windows
MsgBox win.Caption
Next win
Set win = Nothing
End Sub

Good Luck!

Tom Urtis · Apr 10, 2004

This worked for me if I understand you correctly:

Sub Test1()
Dim w As Workbook, x As String
For Each w In Workbooks
w.Activate
x = x & Application.Caption & ", "
Next w
ThisWorkbook.Activate
MsgBox "The captions of all open workbooks in this instance of Excel are:" & vbCrLf & _
x, 64, "Open workbooks caption info..."
End Sub

Jaafar Tribak · Apr 11, 2004

Thanks both of you guys.

Alas, it is not so much the caption of the open workbook windows that I want as to their handles.

I also wanted to get some practice with the FindWindowEx API function because it is much simpler than the EnumChildWindow and doesn't require a callback procedure.

Any other ideas most welcome.

Regards.

Jaafar Tribak · Apr 11, 2004

Hello again,

At last I found out what I was doing wrong : Contrary to what I thought, the Worbook window is not a child window of the excel application window.In fact it is a top level window and therefore its parent window is the Desktop.This means that the first argument of the FindWindowEx must be 0.

Below is the correct code that will return the window handle of each open 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 Sub CommandButton1_Click()

Dim lBookNameLenghth As Long
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)
 MsgBox Msg1 & Counter & vbNewLine & Msg2 & vbNewLine _
 & vbNewLine & vbNewLine & Left(strBuffer, lBookNameLenghth), vbInformation
Loop While lChild

End Sub


As you know, Excel doesn't provide a Handle property for the workbook object (or to any other object apart from the application for that matter), so I was thinking to add this property by combining the above technic and some class module coding.

Regards.

mark007 · Apr 20, 2004

You were on the right track with your first piece of code. The MS-SDIa window is purely a class to display the name in the task bar. It is a top level window if the option is set to show all windows in the taskbar otherwise it doesn't exist. However the Excel7 workbook windows are still created.

The problem you had is that they are children of XLMAIN. Instead they are children of XLDESK which is in turn a child of XLMAIN. You therefore need to add an extra layer to your code and it should work.

See:

http://www.markrowlinson.co.uk/apiwbopen.php

it should help I think.

mark007 · Apr 20, 2004

mark007 said:
The problem you had is that they are children of XLMAIN.

Just to clarify the above should read:

"The problem you had is that they aren't children of XLMAIN."

Unfortunate typing....

sbendbuckeye · Apr 22, 2004

Thanks, Mark!

I just had a client call me this morning about a little custom piece I wrote for him hanging. He's pretty sharp and called me back later to say he had accidently opened it twice and he figured that was causing the problem.

I knew I would have to enumerate the windows somehow because I knew about the GetObject limitation and I also knew that you can open multiple instances of Excel. You just saved me a few hours of work as I am not that proficient when it comes to using the API. THANK YOU AGAIN!

Getting the window handle of all the open workbooks

Jaafar Tribak

Well-known Member

Excel Facts

Jaafar Tribak

Well-known Member

Jaafar Tribak

Well-known Member

sbendbuckeye

Active Member

Tom Urtis

MrExcel MVP

Jaafar Tribak

Well-known Member

Jaafar Tribak

Well-known Member

mark007

New Member

mark007

New Member

sbendbuckeye

Active Member

Similar threads

Forum statistics

Share this page

Getting the window handle of all the open workbooks

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Active Member

MrExcel MVP

Well-known Member

Well-known Member

New Member

New Member

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock