$oExcel.Activecell.Value or .Offset "overheats" and give error

[Donnovan]

I believe this is a COM related question, but as i'm not sure, i posted it here, not in "ActiveX/COM Help and Support (AutoItX)".

I made a very simple script:

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

For $y = 1 to 256
    For $x = 1 to 65000
        $oExcel.Activecell.Value = $x
        $oExcel.Activecell.Offset(1,0).Select
    Next
    $oExcel.Activecell.Offset(-65000,1).Select
Next

This is a image of it running:

There is nothing wrong with this script, but son or not so soon, it gives a error on $oExcel.Activecell.Value or $oExcel.Activecell.Offset, with the reason "The requested action with this object has failed".

Here examples of the errors:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\AutoIt au3\test erro 1.au3"

C:\AutoIt au3\test erro 1.au3 (8) : ==> The requested action with this object has failed.:

$oExcel.Activecell.Offset(1,0).Select

$oExcel.Activecell.Offset(1,0).Select^ ERROR

>Exit code: 1 Time: 250.541

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\AutoIt au3\test erro 1.au3"

C:\AutoIt au3\test erro 1.au3 (8) : ==> The requested action with this object has failed.:

$oExcel.Activecell.Offset(1,0).Select

$oExcel.Activecell^ ERROR

>Exit code: 1 Time: 565.930

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\AutoIt au3\test erro 1.au3"

C:\AutoIt au3\test erro 1.au3 (7) : ==> The requested action with this object has failed.:

$oExcel.Activecell.Value = $x

$oExcel.Activecell^ ERROR

>Exit code: 1 Time: 622.915

Why those erros happens? May be the command $oExcel.Activecell.Value and $oExcel.Activecell.Offset just "overheats" and give a error?

I tryed to check $oExcel with IsObj($oExcel), before the commands, to make sure the Excel object is ok, and it's always ok. Why the request fails?

[Juvigy]

Put a COM error handler and tell us what the error is:

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc") ; put this at the begining of script


Func MyErrFunc()
  Msgbox(0,"Test","Error !"      & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & hex($oMyError.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )

    Local $err = $oMyError.number
    If $err = 0 Then $err = -1
    $g_eventerror = $err  ; to check for after this function returns
Endfunc

Edited June 15, 2011 by Juvigy

[Donnovan]

Here the error:

---------------------------

Test

---------------------------

Error !

err.description is:

err.windescription: A chamada foi rejeitada pelo chamado.

(The call was rejected by the call)

err.number is: 80010001

err.lastdllerror is: 0

err.scriptline is: 41

err.source is:

err.helpfile is:

err.helpcontext is:

---------------------------

OK

---------------------------

It was on this line: If $oExcel.Activecell.Value = $sevEnd Then $end = 1

Edit:

This time the error was in line: $oExcel.Activecell.Offset(0,1).Select

The error info is the same above.

Edited June 15, 2011 by Donnovan

[MrMitchell]

What row and column did it end at? Did you happen to click a cell or hit an arrow key while it was running?

[Donnovan]

The column and row where the error happens change each time. The script run time give you an idea:

>Exit code: 1 Time: 250.541

>Exit code: 1 Time: 565.930

>Exit code: 1 Time: 622.915

After starting the script, i minimize AutoIt and Excel an continue working in other programs, so yes, i hit arrow keys, but not in the Excel windows.

I believe i also tested this example script leaving it work alone, but not sure. Will do it when i get home.

[MrMitchell]

Your script relies on the cell actually being selected so if something outside Excel is somehow deselecting cells even for a moment, crash goes the script. I'm sure you're aware there are better ways to achieve your final results, but I figure it's purposely done this way for one reason or another?

[Donnovan]

I already have tryed a method i believe is better, but the same error happens, althoug it happens on Excel.au3, and not in my Script. This one:

Here the script:

#include <Excel.au3>

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

For $y = 1 to 256
    For $x = 1 to 65000
        _ExcelWriteCell($oExcel,"Cell Text!",$x,$y)
    Next
Next

I ran the script and leaved the computer alone.

On second try i added a line to hide Excel.

Already got the errors, but now it happens on Excel.au3 include script, on the call $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue.

The place of the error, on the call, was different for the two cases: $oExcel.Activesheet^ ERROR and $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue^ ERROR

Try one:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\1TB\AutoIt au3\test erro 1.au3"

C:\Program Files\AutoIt3\Include\Excel.au3 (451) : ==> The requested action with this object has failed.:

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue

$oExcel.Activesheet^ ERROR

>Exit code: 1 Time: 457.757

Try two:

>"C:\Program Files\AutoIt3\SciTE\..\autoit3.exe" /ErrorStdOut "C:\1TB\AutoIt au3\test erro 1.au3"

C:\Program Files\AutoIt3\Include\Excel.au3 (451) : ==> The requested action with this object has failed.:

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue

$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue^ ERROR

>Exit code: 1 Time: 193.835

[Donnovan]

The only remanin idea by me is this one:

With Juvigy erro handler code i see that the script continues to run after the error.

So, based on the line that gave the error ($oMyError.scriptline)i will to execute the code of that line, again, inside MyErrFunc() so then the script can continues like if there was no error.

You guys agree?

[MrMitchell]

When I meant easier way, I meant a little bit faster process... please try:

#include <Excel.au3>

Global Const $xlAutoFill = 4
Global Const $xlDay = 1
Global Const $xlDataSeriesLinear = -4132

$oExcel = _ExcelBookNew()

For $y = 1 to 256
    _ExcelWriteCell($oExcel, 1, 1, $y)
    With $oExcel
        .Activesheet.Range($oExcel.Cells(1, $y), $oExcel.Cells(1, $y)).Select
        .Selection.DataSeries($xlColumns, $xlDataSeriesLinear, $xlDay, 1, 65000, False)
    EndWith
Next

[Donnovan]

Thanks a lot MrMitchell.

Sadly, i can't do it on the real script. This small script was done to simulate the problem.

Talking on the real script, the code repeat on MyErrFunc() worked! Here is it:

Func MyErrFunc()
;  Msgbox(0,"Test","Error !"      & @CRLF  & @CRLF & _
;             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
;             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
;             "err.number is: "         & @TAB & hex($oMyError.number,8)  & @CRLF & _
;             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
;             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
;             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
;             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
;             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
;            )

    If $oMyError.scriptline = 20 Then
        $oInput.Value = $oExcel.Activecell.Value
    ElseIf $oMyError.scriptline = 33 Then
        $oExcel.Activecell.Offset(1,0).Select
    ElseIf $oMyError.scriptline = 35 Then
        $oCell = $oExcel.Activecell.Value
    ElseIf $oMyError.scriptline = 54 Then
        $oExcel.Activecell.Offset( 0 , $x ).Select
    ElseIf $oMyError.scriptline = 58 Then
        $oExcel.Activecell.Offset( 0 , 1 ).Select
    ElseIf $oMyError.scriptline = 62 Then
        $oExcel.Activecell.Offset( 0 , 105 - $y ).Select
    ElseIf $oMyError.scriptline = 66 Then
        $oExcel.Activecell.Offset(0,1).Select
    ElseIf $oMyError.scriptline = 67 Then
        $oExcel.Activecell.Value = $oElement.Value
    ElseIf $oMyError.scriptline = 69 Then
        $oExcel.Activecell.Offset(1,-$y).Select 
    EndIf

    Local $err = $oMyError.number
    If $err = 0 Then $err = -1
    $g_eventerror = $err  ; to check for after this function returns
Endfunc

20, 33, 35, 54, etc... are the lines of the real script that can give error. So if they fail, i repeat the code. The chance to it fail again is minimal, i believe.

Edited June 16, 2011 by Donnovan

[MrMitchell]

Sadly, i can't do it on the real script.

Can't do what on the real script?

So if they fail, i repeat the code. The chance to it fail again is minimal, i believe.

And...that's a very interesting way to handle the problem

[kindlin]

But it still could fail twice in a row, and will eventually. Put a while loop and have it do the loop every cell write and have it continue in the loop until it doesn't fail, then it will happen even if the thing decided to fail like 5 times in a row. I'd bet this will increase your time per cell write, but the program wont ever truly fail.

[Juvigy]

Dont worry - no matter how many times the error happens - the error handler always executes.

I would suggest to try :

1. $oExcel.Visible = 0

at the beginning of your script , and

2. $oExcel.Visible = 1

at the end. This hides the excel window and minimize the chances of errors.

[seangriffin]

I created another topic to deal with this problem. Hope this link helps others who are reading this:

Edited May 22, 2012 by seangriffin