getting the dimensions/location of a workbook

[davidcjmack]

I have been trying to get the RECT of a workbook but it always comes
back wrong - including the formula bar above the workbook!


Here's how I find the handle of the current workbook window :

private IntPtr findWorkbookHwnd(Excel.Application xl, string caption)
{
//Get the main Excel window
IntPtr hWndExcel = new IntPtr(xl.Hwnd);

//Find the desktop
IntPtr XLDesk = User32.FindWindowEx(hWndExcel, IntPtr.Zero,
"XLDESK", IntPtr.Zero);

//Find the workbook window
return User32.FindWindowEx(XLDesk, IntPtr.Zero, "EXCEL7", caption);
}

and here's how I've been getting the Image of the workbook window (from
http://www.developerfusion.co.uk/show/4630/):

private Image getImage(IntPtr Hwnd)
{
// get the hDC of the target window
IntPtr hdcSrc = User32.GetWindowDC(Hwnd);

// get the size
User32.RECT windowRect = new User32.RECT();
User32.GetClientRect(Hwnd, out windowRect);
int top = windowRect.top;
int left = windowRect.left;
int width = windowRect.right - windowRect.left;
int height = windowRect.bottom - windowRect.top;

// create a device context we can copy to
IntPtr hdcDest = GDI32.CreateCompatibleDC(hdcSrc);

// create a bitmap we can copy it to
IntPtr hBitmap = GDI32.CreateCompatibleBitmap(hdcSrc, width,
height);

// select the bitmap object
IntPtr hOld = GDI32.SelectObject(hdcDest, hBitmap);

// bitblt over
GDI32.BitBlt(hdcDest, 0, 0, width, height, hdcSrc, 0, 0,
GDI32.SRCCOPY);

// restore selection
GDI32.SelectObject(hdcDest, hOld);

// clean up
GDI32.DeleteDC(hdcDest);
User32.ReleaseDC(Hwnd, hdcSrc);

// get a .NET image object for it
Image img = Image.FromHbitmap(hBitmap);

// free up the Bitmap object
GDI32.DeleteObject(hBitmap);

return img;
}


but, like I said, I get the wrong screen region back.

It is of the right height and width but it has the wrong x1,y1
coordinates.

Using the windows Accessible Explorer I know that the screen
coordinates of the workbook window are meant to be (321, 580, 920,
887).

The call to
User32.GetClientRect(Hwnd, out windowRect);
returns (0,0,599,317) which is of the right height and width but the
resulting image includes the formula bar.


If I use User32.GetWindowRect(Hwnd, out windowRect) instead, then I get
the coordinates
(555,315,926,338) which I really can't make sense of.


Where on earth am I going wrong?!

 

[NickHK]

This work for me in VBA, giving the same dimension/HWnd as Spy++.

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) 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 Declare Function GetClientRect Lib "user32" (ByVal hwnd As Long,
lpRect As RECT) As Long
Private Declare Function GetWindowRect Lib "user32" (ByVal hwnd As Long,
lpRect As RECT) As Long

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Private Sub CommandButton1_Click()
Dim hwnd As Long
Dim RetVal As Long
Dim Rectangle As RECT
Dim Msg As String

hwnd = FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "XLDESK", vbNullString) _
, 0, "EXCEL7", vbNullString)
If hwnd Then
'RetVal = GetClientRect(hwnd, Rectangle)
RetVal = GetWindowRect(hwnd, Rectangle)
Msg = "My Left is" + Str$(Rectangle.Left) + " Pixels." + Chr$(13) +
"My Top is" + Str$(Rectangle.Top) + " Pixels."
Msg = Msg & vbNewLine & "My Width is" + Str$(Rectangle.Right -
Rectangle.Left) + " Pixels." + Chr$(13) + "My Height is" +
Str$(Rectangle.Bottom - Rectangle.Top) + " Pixels."
MsgBox Msg
Else
MsgBox "Window not found"
End If
End Sub

NickHK

 

[davidcjmack]

I've started from scratch (not that big a deal!) and now I have the
'right' dimensions (i.e. the same as in spy++) so I guess I was just
messing up the output.

I've since realised that the reason it is including the formula bar is
because it is also including the size of the title bar handle (that
appears when a workbook is not maximized).

Not sure how to resize for that...

 

[davidcjmack]

been messing with SystemInformation.Border3DSize and
SystemInformation.CaptionHeight but I can't work out what these
correspond to...

sigh!

 

[NickHK]

David,
Does this give better results ?

Private Sub CommandButton1_Click()
Dim hwnd As Long
Dim RetVal As Long
Dim Rectangle As RECT
Dim Msg As String

Select Case ActiveWindow.WindowState
Case xlMinimized
MsgBox "N/A"
Exit Sub
Case xlMaximized
'Get dimensions of "XLDESK" windows
hwnd = FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "XLDESK", vbNullString)
Case xlNormal
'OK, use WS window
hwnd = FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "XLDESK", vbNullString) _
, 0, "EXCEL7", vbNullString)
End Select

If hwnd Then
'RetVal = GetClientRect(hwnd, Rectangle)
RetVal = GetWindowRect(hwnd, Rectangle)
Msg = "My Left is" + Str$(Rectangle.Left) + " Pixels." + Chr$(13) +
"My Top is" + Str$(Rectangle.Top) + " Pixels."
Msg = Msg & vbNewLine & "My Width is" + Str$(Rectangle.Right -
Rectangle.Left) + " Pixels." + Chr$(13) + "My Height is" +
Str$(Rectangle.Bottom - Rectangle.Top) + " Pixels."
MsgBox Msg
Else
MsgBox "Window not found"
End If
End Sub

Out of interest, is this for some kind of screen/window capture ?

NickHK

 

[davidcjmack]

Yes, it is for a screen capture.

Unfortunately I've not been able to try out your code suggestion, as
somebody else has taken over the machine I was working on - for the
next few days at least.