Jump to content

Excel Bug (COM)[Solved]


Fulano
 Share

Recommended Posts

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 by Fulano

#fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja!

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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

#fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja!

Link to comment
Share on other sites

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

#fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja!

Link to comment
Share on other sites

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 :D

Fulano

#fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja!

Link to comment
Share on other sites

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

#fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja!

Link to comment
Share on other sites

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

; 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

Link to comment
Share on other sites

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 by Fulano

#fgpkerw4kcmnq2mns1ax7ilndopen (Q, $0); while ($l = <Q>){if ($l =~ m/^#.*/){$l =~ tr/a-z1-9#/Huh, Junketeer's Alternate Pro Ace /; print $l;}}close (Q);[code] tag ninja!

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...