Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > getting the dimensions/location of a workbook
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default getting the dimensions/location of a workbook
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?!

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default getting the dimensions/location of a workbook
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



wrote in message
ps.com...
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?!



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default getting the dimensions/location of a workbook
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...



NickHK wrote:
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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default getting the dimensions/location of a workbook
been messing with SystemInformation.Border3DSize and
SystemInformation.CaptionHeight but I can't work out what these
correspond to...

sigh!


wrote:
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...



NickHK wrote:
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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default getting the dimensions/location of a workbook
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

wrote in message
ups.com...
been messing with SystemInformation.Border3DSize and
SystemInformation.CaptionHeight but I can't work out what these
correspond to...

sigh!


wrote:
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...



NickHK wrote:
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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7
Default getting the dimensions/location of a workbook
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.

NickHK wrote:
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

wrote in message
ups.com...
been messing with SystemInformation.Border3DSize and
SystemInformation.CaptionHeight but I can't work out what these
correspond to...

sigh!


wrote:
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...



NickHK wrote:
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
copying chart to location within same workbook HelenR Charts and Charting in Excel 2 November 12th 09 02:43 AM
Hyperlink to workbook location jaclh2o Excel Worksheet Functions 0 September 23rd 08 08:28 PM
define one cell location throughout workbook? New_to_accounting Excel Worksheet Functions 3 September 2nd 06 12:36 AM
delete workbook from one location and save workbook to new locatio Damien Excel Programming 5 August 3rd 06 03:05 PM
ThisWorkbook.FollowHyperlink to Location in Workbook James Cox[_2_] Excel Programming 8 April 25th 05 03:21 PM


All times are GMT +1. The time now is 03:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement