Testing for an Excel "busy" signal

Does Excel provide a simple method or a property that will return a value or message indicating if Excel is "busy"? By busy I mean Excel has a dialog box open and is currently active or if the Print Preview has the focus.

I apologize if this is a FAQ but I have extended a considerable amount of time investigating this without locating a satisfactory solution. I have attempted invoking Excel commands for several different scenarios where Excel is displaying various dialog boxes or as I mentioned above the Print Preview window. In order to anticipate the various possible states of Excel I am forced to write "Try.. Catch System.Runtime.InteropServices.COMException" code at numerous locations with in my program.

Can Excel return a "busy" signal via a method or property call?

Thank You,

Jim

No, not as far as I know. I'll ask around though, but I don't think that there is any mechanism to deal with this. Including the use of Error Handling...

So, I'm curious, then, that you managed to have Try..Catch actually throw an exeption? I had always thought that commands would sit, waiting for the "busy" mode to pass, and then execution would immediately resume. But if the User left a Dialog Box open and then went out for lunch, then you'd be stuck...

If you got Try..Catch to work for you (1) I'd be happy with that (2) please show your code, I wanna learn! :) and (3) I will ask among the other Excel Experts here if they know of a mechanism, to detect "busyness", but I don't personally know of any...

-- Mike

Hi,

I haven´t been exploring it for .Net at all but I felt I could at least provide an example of one VB 6.0 approach that may / may not be of interest for You - it deals with edit-mode.

(I´m not the creator of it but I has used a revised version of it in an application last year).


Option Explicit

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Sub Command1_Click()
App.OleServerBusyRaiseError = True
App.OleServerBusyTimeout = 1000

Dim xlApp As Excel.Application
Set xlApp = New Excel.Application

With xlApp
.Visible = True
.Workbooks.Add
End With

'Sleep for a bit so the tester can put a cell into edit-mode
Sleep 10000

On Error GoTo ErrorHandler

With xlApp
.ActiveSheet.Cells(5, 5).Value = 47
.Workbooks.Close
.Quit
End With

Exit Sub

ErrorHandler:
If Err = &H80010001 Then
AppActivate "Microsoft Excel"
'Break the edit-mode.
'SendKeys "{Esc}"
MsgBox "Excel is in edit-mode"
Resume
End If
End Sub


Kind regards,
Dennis

Dennis: could you explain what your example does, I don't follow :-\ :) Also the property 'OleServerBusyRaiseError' doesn't seem to feature in my version of Excel 2003.

BridgeGuy: could you specify exactly how Excel qualifies as busy for your purposes? Is it limited exclusively to a built-in dialog being open, or the print preview being open, or are there other proviso's as well?..

Timbo,


App.OleServerBusyRaiseError = True
App.OleServerBusyTimeout = 1000


are VB6 commands rather than VBA, hance you can't find them in your Excel ;)

It just tells VB to throw a runtime error if the server (excel) takes too long to respond. The rest I'm sure you can follow!

:)

Ok, well, BridgeGuy is using .Net, so there is very likely a similar capability... I just don't know enough here to say what the command would be.

That said, in my automation tests (Automating from Word VBA in this case) I was freely able to manipulate the oExcel instance even if the User were editing a cell. (Which shocked me.) I could even call oExcel.ActiveCell.Value = "Hello" while the cell was being edited!!

The code does "wait" however, if the User opens up a DialogBox or the like.

I'm going to try to track down the .Net equilalent to App.OLEServerBusyRaiseError and App.OLEServerBusyTimeOut. If anyone knows what this would be, please post it here! This would be the last piece of the puzzle...

-- Mike

This is what I wrote a while back to check for excel dialogboxes in VB 6. With a little bit of work it may work for .Net.

Private Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Function CheckForXLDialogBox() as Long
Dim ThreadID As Long, ProcessID As Long ' receive id to thread and process of Form1
Dim xlWnd As Long, retVal as Long
' Determine the thread which owns this window
xlWnd = FindWindow("XLMAIN", vbNullString)
ThreadID = GetWindowThreadProcessId(xlWnd, ProcessID)
' Use the callback function to list all of the enumerated thrad windows
EnumThreadWindows ThreadID, AddressOf EnumThreadWndProc, 0
'bosa_sdm_xl# is the classname for the dialogbox
If InStr(LCase(sClasses), "bosa_sdm_xl") Then
retVal = -1
End If
CheckForXLDialogBox = retVal
End Sub

'In a module

'In a module
Declare Function GetWindowThreadProcessId Lib "user32" _
(ByVal hWnd As Long, lpdwProcessId As Long) As Long
Declare Function EnumThreadWindows Lib "user32" _
(ByVal dwThreadId As Long, ByVal lpfn As Long, _
ByVal lParam As Long) As Long
Declare Function GetClassName Lib "user32" Alias _
"GetClassNameA" (ByVal hWnd As Long, ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long

Public sClasses As String
Public Function EnumThreadWndProc(ByVal hWnd As Long, _
ByVal lParam As Long) As Long
Dim Ret As Long, sText As String
'create a string-buffer
sText = Space(255)
'get the classname of the window handle
Ret = GetClassName(hWnd, sText, 255)
'cut off the unnecessary part of Chr$(0)'s
sText = Left$(sText, Ret)
'add this classname to the list of classnames
sClasses = sClasses + sText + vbCrLf
'continue the enumeration
EnumThreadWndProc = 1
End Function