I'm embedding Excel in a VB app, and I've found that when the user is editing the formula in a cell, that certain automation calls hang - they bring up VB's Switch To/Retry/Cancel dialog. Is there any way to programmatically click the checkmark to get out of formula edit mode so that the document may manipulated? To reproduce, create a form with a button and a webbrowser control. Add the following code:

Private Sub Command1_Click()
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim w As Excel.Window

Set wb = WebBrowser1.Document
Set ws = wb.ActiveSheet
Set w = wb.Windows(1)
End Sub

Private Sub Form_Load()
WebBrowser1.Navigate2 "file:///C:/test.xls"
End Sub

Hi

You may try to take a look at the link below.

Considerations When Using the WebBrowser Control
The WebBrowser control navigates to documents asynchronously. This means
that when you call WebBrowser1.Navigate, the call returns control to your
Visual Basic application before the document has been completely loaded. If
you plan on Automating the contained document, you need to use the
NavigateComplete2 event to know when the document has finished loading. Use
the Document property of the WebBrowser object passed in to get a reference
to the Office document object, which, in the preceding code, is set to
oDocument.

How To Use the WebBrowser Control to Open an Office Document
http://support.microsoft.com/default...B;EN-US;243058

Also you may try to move the code that open the office document into the
command1_click, because in the form_load the vb6 is initializing the form's
status, which may cause problem, or you may try to call DoEvents in the
end of the form_load.

You may have a try and let me know the result.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi,

This is caused by that the Excel Application is STA which handle the com
automation command based on message queue.
When we enter the editor mode of the formula, the focus will be changed to
the fomular bar window and it will run a message loop to handle the
message. In the meanwhile the com call incoming will pend because the
fomular bar message loop will not handle the com call message, till the
fomular bar lost focus and the excel main window will get the focus and run
the message loop to handle the com call, if in a predefined time period,
the focus did not change back to the excel main window, the error dialog
will pop up the vb client did not get response from excel com server in a
predefined time out value.

So I think we would better not call the automation code when we have
entered the certain editor mode, e.g. the fomular bar, the cell editor mode
and so on.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Peter,

Thank you for the response, we're on the right track but we have a long way to go.

First question is, OK, you say we should avoid calling the automation code in this case, but how do we detect that we are in this situation, that the formula bar is open? What automation call will tell me, without hanging itself?

The second problem is that this really doesn't satisfy the users. The users expect to be able to move on without explicitly closing the formula bar. For example, in the application, they can choose Save and the formula is closed automatically. So there must be a way, once we have detected this situation, to handle it. Perhaps by calling the win32 sendmessage api directly?

Thanks,
-Stewart

Hi,

As I said before, when we are in the edit mode, we can not know which excel
childwindow has the input focus, because in this time all the automation
code will hang. If you really want to do this, I think you may try to
simulate the press the escape key to cancel the editor mode. But this is
not recomnended and the method may be ugly.

Here goes the code, just for your reference.

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 FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetFocus1 Lib "user32" Alias "SetFocus" (ByVal
hwnd As Long) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA"
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam
As Long) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA"
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam
As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32.dll" _
Alias "RtlMoveMemory" (Destination As Any, Source As Any, _
ByVal Length As Long)
Private Type KEYBDINPUT
wVk As Integer
wScan As Integer
dwFlags As Long
time As Long
dwExtraInfo As Long
End Type

Private Type INPUT_TYPE
dwType As Long
xi(0 To 23) As Byte
End Type

Private Const KEYEVENTF_KEYUP = &H2
Private Const KEYEVENTF_EXTENDEDKEY = &H1
Private Const VK_SHIFT = &H10
Private Const VK_HOME = &H24
Private Const VK_END = &H23
Private Const INPUT_KEYBOARD = 1

Private Declare Function MapVirtualKey Lib "user32" _
Alias "MapVirtualKeyA" (ByVal wCode As Long, _
ByVal wMapType As Long) As Long

Private Declare Function SendInput Lib "user32.dll" _
(ByVal nInputs As Long, pInputs As INPUT_TYPE, _
ByVal cbsize As Long) As Long

Const WM_KEYDOWN = &H100
Const WM_KEYUP = &H101
Const VK_RIGHT = &H27
Const VK_RETURN = &HD
Const VK_ESCAPE = &H1B
Private Sub Command1_Click()
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim w As Excel.Window
App.OleServerBusyRaiseError = True
App.OleServerBusyTimeout = 1000
App.OleServerBusyMsgText = ""
Set wb = WebBrowser1.Document
On Error GoTo ErrorHandler
Debug.Print wb.Application.ActiveWindow.Caption
Exit Sub
ErrorHandler:
Debug.Print Err.Description
Call Command3_Click
wb.Application.Cells(1, 1) = "c"
End Sub

Private Sub Command3_Click()
Dim hwnd As Long
hwnd = FindWindow("ThunderFormDC", vbNullString)
hwnd = FindWindowEx(hwnd, 0, "Shell Embedding", vbNullString)
SetFocus1 hwnd
Dim wVkKey(1) As Integer
Dim UpDown(1) As Integer
wVkKey(0) = VK_ESCAPE: UpDown(0) = 0
wVkKey(1) = VK_ESCAPE: UpDown(1) = 1
sKeyEventSet 2, wVkKey, UpDown
End Sub

Private Sub Form_Load()
WebBrowser1.Navigate2 "file:///C:/test.xls"
End Sub
Private Sub sKeyEventSet(nInput As Long, _
wVkKey() As Integer, UpDown() As Integer)
Dim inputevents() As INPUT_TYPE
Dim keyevent As KEYBDINPUT
Dim Count As Integer
ReDim inputevents(nInput - 1) As INPUT_TYPE
For Count = 0 To nInput - 1
With keyevent
.wVk = wVkKey(Count)
.wScan = MapVirtualKey(wVkKey(Count), 0)
If UpDown(Count) = 0 Then
.dwFlags = KEYEVENTF_EXTENDEDKEY Or 0
Else
.dwFlags = KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP
End If
.time = 0
.dwExtraInfo = 0
End With
inputevents(Count).dwType = INPUT_KEYBOARD
CopyMemory inputevents(Count).xi(0), keyevent, Len(keyevent)
Next Count
Dim rt As Long
rt = SendInput(nInput, inputevents(0), Len(inputevents(0)))
Debug.Print rt
Debug.Print Err.LastDllError
End Sub



Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Stewart,

Because SendMessage will do the same job as we automation work that it will
put a message in the excel message queue, so it does not work as Excel is
working on the focused window(something like a modal dialog), so we use
sendinput which will generate the input event in the input queue directly.

Anyway I am glad that the project worked!!
Cheers!

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.