Object Busy (1 Viewer)

[PearlGI]

I have a database that manipulates data in an Excel workbook. Everything works okay, unless Excel is 'busy', for instance if a user has left up an open file dialog window and then everything hangs.

Is it possible to identify if objExcel is 'busy' so that the user can be asked to rectify the problem so that the database can perform its tasks?

If anybody wants to see what happens, try the running the following code from Access whilst an Open File dialog box is open in Excel.

Sub TestXL()
Dim objExcel As Excel.Application, _
    wb As Workbook
Set objExcel = GetObject(, "Excel.Application")
Set wb = objExcel.Workbooks.Add
End Sub

ps. You need to make sure than the Microsoft Excel Object Library is referenced for this code to run.

 

[jcarlier]

maybe, you can retrieve some information to your problem:

http://www.tek-tips.com/viewthread.cfm?qid=1448515&page=11

 

[PearlGI]

Thanks, but that article is about detecting if Excel and/or a workbook is already running. (Already have all those bases covered)

I'm looking for something that detects is Excel is off doing something else which prevents another app from taking control. Mostly likely caused by an Excel dialog window.

 

[PearlGI]

I've adapted the following article to fix my problem and posted the code in case anyone else is interested.

http://www.xtremevbtalk.com/archive/index.php/t-150302.html

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


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

Sub TestXL()
Set objExcel = GetObject(, "Excel.Application")
Do While CheckForXLBusy
'    objExcel.WindowState = vbMaximizedFocus'
    MsgBox "Excel is busy", vbExclamation
Loop
Set wb = objExcel.Workbooks.Add
End Sub


Function CheckForXLBusy() 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'
sClasses = ""
xlWnd = FindWindow("XLMAIN", vbNullString)
ThreadID = GetWindowThreadProcessId(xlWnd, ProcessID)
' Use the callback function to list all of the enumerated thread windows'
EnumThreadWindows ThreadID, AddressOf EnumThreadWndProc, 0
'bosa_sdm_xl# is the classname for the dialogbox'
'#32770 is the classname for the update on open dialogbox'
If InStr(LCase(sClasses), "bosa_sdm_xl") Or _
    InStr(LCase(sClasses), "#32770") Then
retVal = -1
End If
CheckForXLBusy = retVal
End Function

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

The other Excel busy state that I haven't been able to work out, is if Excel is editing a cell. Any solutions, please add.