Excel Bug (COM)[Solved]

[Fulano]

Hi, I'm working on some macros for transferring contact data between Excel and ACT!. I've got most all of the bugs worked out. Unfortunately, there is one bug that I can't seem to get rid of.

Basically, the macro fails if any call is made while a cell is open for editing. I've created a sample script to recreate the problem:

Local $oExcel = ObjGet("", "Excel.Application")
If @error Or Not IsObj ($oExcel) Then
    MsgBox (0, "ERROR", "Excel is not availible")
    Exit
EndIf

$oExcel.Selection.Hyperlinks.Delete ()

This happens with the Excel UDF as well, but for simplicities sake this script doesn't use it.

I've tried to test for this condition, but the tests throw the bug as well. The approach I'm currently working on is to send a {Esc} to the active control (doesn't work atm).

ControlSend ("[Class:Excel]", "", ControlGetFocus ("[Class:Excel]"), "{Esc}")

Any suggestions?

Edited April 27, 2010 by Fulano

[zfisherdrums]

It isn't an answer, but here's some further details that might explain why this behavior exists.

http://social.msdn.microsoft.com/Forums/en/isvvba/thread/3333e18b-cef3-4d78-b47a-6916a1b2d84c

Zach...

[Fulano]

It isn't an answer, but here's some further details that might explain why this behavior exists.

http://social.msdn.microsoft.com/Forums/...ead/3333e18b-cef3-4d78-b47a-6916a1b2d84c

Zach...

Thanks Zach, it's good to know it's not just me. They detailed a few ways around it, I'll post them here if they work.

Fulano

[Fulano]

UPDATE: I've tried the two workarounds they offered, both fail before execution. I think this might be a permanent headache.

I'll post if I come up with something useful

[Fulano]

I've found something that works (sort of)

Make a script containing the following:

Local $handle = ObjEvent("AutoIt.Error","dummyFunc")

Local $oExcel = ObjGet("", "Excel.Application")
$oExcel.Application.Ready

Func dummyFunc ()
    $handle.clear()
    Exit (1)
EndFunc

and save it as "ExcelChecker.au3"

Then call

ShellExecuteWait ($pathToScript & "\ExcelChecker.au3")

It will fail, returning 1 if Excel is editing or otherwise unavailable, or 0 if everything is ok.

Horrible solution, but it should work reliable. The misery is now your script has a fixed location dependency

Fulano

[Fulano]

I found a better way of controlling the floating file, so it's a little less hassle.

Basically it installs the script to your Application Data for you, checks to make sure it is there once at run-time.

It's pretty well behaved, and warns you if it needs to install, what it is installing, and where.

Files: ExcelChecker.au3 <- Include file

Fulano

PS: I didn't bother to comment it. It should be pretty straightforward, if not let me know and I'll clean it up ... probably.

ExcelChecker.au3

[picaxe]

This works for me (Excel 2003) without the need for additional script.

expandcollapse popup

; example tested with Excel 2003

; instructions
; open a new excel spreadsheet
; double click on any cell to enter edit mode
; run this script
; press esc to exit edit mode or wait for time out

Global $iEditMode = False
$oComErr = ObjEvent("AutoIt.Error","ComErr")
$oExcel = ObjGet("", "Excel.Application")
If @error Or Not IsObj($oExcel) Then
    MsgBox(262144, "", "Excel is not availible")
    Exit
EndIf

If EditMode() Then
    ConsoleWrite("Exit edit mode via ControlSend" & @LF)
    ControlSend ("[Class:XLMAIN]", "", ControlGetFocus ("[Class:XLMAIN]"), "{Esc}")
Else
    ConsoleWrite("Excel is NOT in edit mode" & @LF)
EndIf

; do something
$oExcel.ActiveSheet.Range("A1").Value = Int(Random(1000, 90000))


Func EditMode($iTimeout = 10)   ; default Timeout = 10 secs
    $iTimeOut = $iTimeOut * 5
    $i = $iTimeOut
    While 1
        $iEditMode = False
        $oExcel.Application.Ready
        Sleep(200)
        If Not $iEditMode Or $i = 0 Then ExitLoop
        If $i = $iTimeOut Then ConsoleWrite("Waiting for exit from edit mode or timeout" & @LF)
        $i -= 1
    WEnd
    Return $iEditMode   ; return 1=in edit mode, 0=not in edit mode
EndFunc

Func ComErr()
    If StringInStr($oComErr.WinDescription, "call was rejected by callee") Then
        $iEditMode = True
        Return
    EndIf
    $ErrorOutput = @LF & "COM Error in " & @ScriptName _
             & @LF & "Line = " & $oComErr.Scriptline _
             & @LF & "Hex = " & Hex($oComErr.Number, 8) _
             & @LF & "Numb = " & $oComErr.Number _
             & @LF & "WinDesc = " & StringStripWS($oComErr.WinDescription, 2) _
             & @LF & "Desc = " & StringStripWS($oComErr.Description, 2) _
             & @LF & "Src = " & $oComErr.Source _
             & @LF & "HlpFile = " & $oComErr.HelpFile _
             & @LF & "HlpContx = " & $oComErr.HelpContext _
             & @LF & "LastDllErr = " & $oComErr.LastDllError
    MsgBox(262144 + 16, @ScriptName, $ErrorOutput)
;~  $fEventError = 1 ; something to check for when this function returns
EndFunc ;==>ComErr

[Fulano]

That looks quite a bit more elegant, I'll test it against Excel 2007 and get back with the results.

Update: Good news, it works with Excel 2007 as well. I checked Locodarwin's UDF and it doesn't register an error handler, so this might be worth submitting for inclusion into the UDF

Edited February 22, 2010 by Fulano