Sign in to follow this  
Followers 0
Donnovan

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

14 posts in this topic

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:

Posted Image

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?

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#14 ·  Posted (edited)

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

Edited by seangriffin

Cheers, Sean.

See my other UDFs:

Chrome UDF - Automate Chrome | SAP UDF - Automate SAP | Java UDF - Automate Java Applications & Applets | Tesseract (OCR) UDF - Capture text from applications, controls and the desktop | Textract (OCR) UDF - Capture text from applications and controls | FileSystemMonitor UDF - File, Folder, Drive and Shell Monitoring | VLC (Media Player) UDF - Creating and controlling a VLC control in AutoIT | Google Maps UDF - Creating and controlling Google Maps (inc. GE) in AutoIT | SAPIListBox (Speech Recognition) UDF - Speech Recognition via the Microsoft Speech (SAPI) ListBox | eBay UDF - Automate eBay using the eBay API | ChildProc (Parallel Processing) UDF - Parallel processing functions for AutoIT | HyperCam (Screen Recording) UDF - Automate the HyperCam screen recorder | Twitter UDF - Automate Twitter using OAuth and the Twitter API | cURL UDF - a UDF for transferring data with URL syntax

See my other Tools:

Rapid Menu Writer - Add menus to DVDs in seconds | TV Player - Automates the process of playing videos on an external TV / Monitor | Rapid Video Converter - A tool for resizing and reformatting videos | [topic130531]Rapid DVD Creator - Convert videos to DVD fast and for free | ZapPF - A tool for killing processes and recycling files | Sean's eBay Bargain Hunter - Find last minute bargains in eBay using AutoIT | Sean's GUI Inspector - A scripting tool for querying GUIs | TransLink Journey Planner with maps - Incorporating Google Maps into an Australian Journey Planner | Automate Qt and QWidgets | Brisbane City Council Event Viewer - See what's going on in Brisbane, Australia

Share this post


Link to post
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
Sign in to follow this  
Followers 0