asked on

Wrapped COM application (Excel) not responding to input without a double click. Visual Basic .NET

INTRODUCTION
-------------------
I have built a control which wraps an excel application into my own application over the past month or so. I've met many challenges, but have solved all of them until now.

A picture of my application is attached.

As you can see, Excel has the illusion of being smoothly integrated into my excel application.

PROBLEM
------------
The problem is that the application will not receive input of any kind until it has been double clicked.

RESOLUTIONS ATTEMPTED
---------------------------------
I've attempted to capture every event the program fires. All of the activate and deactivate events (there are 8 different ones in total) only fire when the file is first opened and closed, but do not fire when focus shifts from my application to the excel window or any other window on the computer. The one event that does fire repeatedly is the xlApp.SheetSelectionChange event. Thus if they select a cell on the worksheet (other than the one last selected), I have a chance to run some code and try to get the application ready for input.

I've tried every form of input, but unless a double click event has been triggered in the excel application, it will not notice any key being pressed, or the mouse scroll wheel. I can click around all I want, but no other form of input will work until a double click has been triggered, not even automated input - xlApp.Sendkeys has no effect and SendKeys.Send has no effect on the application.

QUESTION
-------------
Can anyone think of any ways of making this application work normally. The likely solution will require some way of sending an automated double click event at the co-ordinates that the user clicked, followed by the escape key ( xlApp.SendKeys("{ESC}") ) so that the user is none the wiser, and doing so in the SheetSelectionChange event.

Thank you. I've attached some code to help start things off.

--
Alain Bryden

'Once upon a time'
Public Function OpenApp()
    Private WithEvents xlApp As Excel.Application
    ...
    xlApp = New Excel.Application
    Private appHWnd As Integer = xlApp.Hwnd
    SetParent(CType(appHWnd, IntPtr), Me.Handle)
    'The following hack (courtesy of Microsoft) is supposed to solve the initial UI unresponsiveness'
    'But even after sending these two keys, the double click is required each time excel loses focus' 
    SendKeys.Send(" ")
    SendKeys.Send("{ESC}")
    ...
 
'Elsewhere...'
    'To make the excel window fit so nicely into the custom control (called ExcelHoster):'
    Private Sub ExcelHoster_Resize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Resize
        Dim Swidth As Integer = SystemInformation.Border3DSize.Width
        Dim Sheight As Integer = SystemInformation.Border3DSize.Height
        ...
        If Not xlApp Is Nothing Then _
            MoveWindow(appHWnd, -2 * Swidth, 0 * Sheight - Cheight - Theight, _
                       Me.Width + 4 * Swidth, Me.Height + Cheight + 2 * Sheight + Theight, True)
    End Sub
 
'This event will likely need to solve my problem:'
    Private Sub UserClicked() Handles xlApp.SheetSelectionChange
        'xlApp.SendKeys("{F2}") ''Useless'
        'Code required to resume normal UI functionality (i.e. double click event fire)'
        xlApp.SendKeys("{ESC}") 'Useless until double click event has fired'
    End Sub

Open in new window

Demo-Image.PNG

William Elliott

just a thought,..
can you create another sheet and have the second sheet be the first one that shows

then use code to change to the "all countries" sheet and hide the secondary sheet at the same time
thus allowing the sheet selection change event to fire?

alainbryden

ASKER

Yes, I could do that, and there are times when I do have multiple worksheets, but this doesn't make the program any more responsive when entering use edit mode.

Also that wouldn't fire the sheetSelectionChange event. That event specifically fires when the user selects a new cell within some sheet. It would technically fire the sheetActivate/Deactivate event, but that doesn't help me at all. I'm already getting the sheetSelectionChange event firing fine when the user clicks into the Excel environment, I just need it to do something productive so that the environment becomes responsive.

Howard Cantrell

Why not make a datgridview, load it and save the view to excel if you need to keep it in excel form.

alainbryden

ASKER

Because it's aesthetically unpleasing and completely unsuited from what I'm attempting to accomplish.

Howard Cantrell

try this code....

'mouse api & TYPE
Declare Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
 
Type POINTAPI
  x As Long
  y As Long
End Type
 
'window apis
Private Declare Function ScreenToClient Lib "user32" (ByVal hwnd As Long, lpPoint As POINTAPI) As Long
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 Const LOGPIXELSX = 88 ' Logical pixels/inch in X
Private Const LOGPIXELSY = 90 ' Logical pixels/inch in Y
' screen settings
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDc As Long, ByVal nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hDc As Long) As Long
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
 
Function mousepos() As POINTAPI
 
Dim mouse As POINTAPI
Dim hDc As Long, lngRetVal As Long
Dim varScreenX As Variant, varScreenY As Variant
Dim varPixToInchX As Variant, varPixToInchY As Variant
 
hDc = GetDC(0)
 
    'Get the conversion of pixels/inch for X & Y
varPixToInchX = GetDeviceCaps(hDc, LOGPIXELSX)
varPixToInchY = GetDeviceCaps(hDc, LOGPIXELSY)
 
lngRetVal = ReleaseDC(0, hDc)
ret = GetCursorPos(mouse)
 
If ActiveWindow.WindowState = xlMaximized Then
    hWndXL = FindWindow("XLMAIN", Application.Caption)
    hWndDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
    ret = ScreenToClient(hWndDesk, mouse)
Else
    hWndXL = FindWindow("XLMAIN", Application.Caption)
    hWndDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
    hWndXLE = FindWindowEx(hWndDesk, 0&, vbNullString, ActiveWindow.Caption)
    ret = ScreenToClient(hWndXLE, mouse)
End If
 
x = (mouse.x / varPixToInchX) * 72
y = (mouse.y / varPixToInchY) * 72
 
mousepos.x = x
mousepos.y = y
 
 
End Function

Open in new window

alainbryden

ASKER

hWndXL = FindWindow("XLMAIN", Application.Caption) isn't necessary as I already have this handle through application.hwnd

Now, while
-----------------------------
Structure POINTAPI : Public x As Long : Public y As Long : End Structure
Declare Function GetCursorPos Lib "user32.dll" (ByVal lpPoint As POINTAPI) As Long

Dim mousepos As POINTAPI
GetCursorPos(mousepos)
-----------------------------

This particular chunk of code proves useful in finding the mouse point, it doesn't help me generate a feed an automated double click into the interface. Since this is only being triggered when the user clicks the application, all I need to do is duplicate their click immediately afterwards.

alainbryden

ASKER

GetCursorPos is giving (0,0) constantly. It's returning a nonzero return value so it's returning successfully, but the point is always (0,0)

Howard Cantrell

yea i have been trying to see why it's doing that.

alainbryden

ASKER

I am still in the same position - I am trying to discover code that will locate the current position of the mouse, and then simulate a user double clicking at that position (ie generate a mouse down, mouse up, mouse down, mouse up event at that same position). The event doesn't necessarily need to be injected into the excel window only, although I suppose it would be more proper. I would settle for simply injecting a system-wide double click event and expecting that the excel application falls under the mouse at that position.

alainbryden

ASKER

I'd personally prefer to leave it open a while. Someone with a solution may come along some day and the work I've already done in the question description may be useful to someone trying to accomplish something similar.

William Elliott

why not maximize the app, get the screen size then ahve the double click fire in the middle of the screen?

or since the excel app connects to the bottom right of the window, get that location and move it a certain distance up and left to get the innner corner to avtivate it?

alainbryden

ASKER

That's not even necessary. I can easily find a position on the screen at which I wish to have the double click fire - I just have no way to fire a double click.

ASKER CERTIFIED SOLUTION

William Elliott

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

alainbryden

ASKER

Good find my friend, I'll check it out.