Jump to content
Juvigy

Strange Excel script error and behaviour

Recommended Posts

Juvigy

 Hi Guys,

I am comparing 2 columns cell by cell in excel and writing the result to a third one. The strange thins is that after 8 passes of my main script i get an error. Here is my code:

 

Global $search,$test
;~ $j=0
$oExcel = ObjGet("", "Excel.Application")
For $i=53 to 159 Step 1
    $search = $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,1).Value
    For $j=2 to 513 Step 1
        $test = $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($j,5).Value
        If StringRegExp($test,$search)=1 AND $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value = "" Then
            $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value = $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($j,5).Value
            ContinueLoop
        EndIf
        If StringRegExp($test,$search)=1 Then
            $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value = $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value &"|"& $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($j,5).Value
        EndIf
    Next
    IF $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value = "" Then $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value = "#N/A"
    ConsoleWrite($i&">>"&$j&@CRLF)
Next

And here is the error:

We intercepted a COM Error !on i=53 and j=324
err.description is:     
err.windescription: Exception occurred.

err.number is:  80020009
err.lastdllerror is:    0
err.scriptline is:  18
err.source is:  
err.helpfile is:    
err.helpcontext is:     0

As i have an error handler the script continues and then i get errors continuously from that moment  on:

We intercepted a COM Error ! on i=53 j=325
err.description is:     
err.windescription: Bad variable type.

err.number is:  80020008
err.lastdllerror is:    0
err.scriptline is:  18
err.source is:  
err.helpfile is:    
err.helpcontext is:

The line i got the errors is always:

$test = $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($j,5).Value

Anyone has any idea why this happens?

Edited by Juvigy
update

Share this post


Link to post
Share on other sites
Subz

Can you post working example and example file?  Why not just use a formula in the third cell and copy and paste to the remaining cells?

Share this post


Link to post
Share on other sites
Juvigy

I cant post the file - it is confidential information. First column contain 150 account names - second column contains 500 variation of the account names. I want to combine the repeating variations to a third column as Variation1+"|"+Variation2. This will be too complicated for an excel formula. I have noticed something strange - the script brakes after
around 4000 passes in the loops - ;4135;4136;4119 those are the last 3 tries while increment $i manually with 8.

Share this post


Link to post
Share on other sites
water

Which version of AutoIt do you run?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Juvigy

SciTE Version 3.7.3  , Autoit v3.3.14.2

I manually incremented $i to finish the work. I got the errors after the following iteration numbers that look suspiciously close: ;4135;4136;4119;4134;4135;4133;4134 

Once i got an Autoit crash with  AutoIt3.exe ended.rc:-1073741819. Can this be related to X86 - X64? I will test further.

Edited by Juvigy
Update

Share this post


Link to post
Share on other sites
water

You should do a check for @error after each Excel method you call. This allows to log the values of relevant variables and application status.

Global $search,$test
;~ $j=0
$oExcel = ObjGet("", "Excel.Application")
If @error Then Exit ConsoleWrite("Error opening Excel. @error = " & @error & ", @extended = " & @extended & @CRLF)
For $i=53 to 159 Step 1
    $search = $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,1).Value
    If @error Then Exit ConsoleWrite("Error getting value of cell. $i = " &i & ", @error = " & @error & ", @extended = " & @extended & @CRLF)
    For $j=2 to 513 Step 1
        $test = $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($j,5).Value
        If @error Then Exit ConsoleWrite("Error getting value of cell. $i = " &i & ", $j = " &j & ", @error = " & @error & ", @extended = " & @extended & @CRLF)
        If StringRegExp($test,$search)=1 AND $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value = "" Then
            $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value = $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($j,5).Value
            ContinueLoop
        EndIf
        If StringRegExp($test,$search)=1 Then
            $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value = $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value &"|"& $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($j,5).Value
        EndIf
    Next
    IF $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value = "" Then $oExcel.Application.ActiveWorkbook.Sheets(1).Cells($i,4).Value = "#N/A"
    ConsoleWrite($i&">>"&$j&@CRLF)
Next

You get the picture ... ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Another idea is to read the whole Excel sheet into an array, process the array and only write altered cells back to Excel. Much faster :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
Juvigy

Hi Water, Why should i do the @error check on every line? I have the error handler for that:

Func MyErrFunc()
  Msgbox(0,"TicketSurvey Error!","Error Details:"   &$b   & @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, 20)
  ConsoleWrite ("We intercepted a COM Error !"      &$i& @CRLF  &$j& @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 & @CRLF )
    Local $err = $oMyError.number
    If $err = 0 Then $err = -1
    $g_eventerror = $err  ; to check for after this function returns
Endfunc

 

Share this post


Link to post
Share on other sites
Juvigy

Looks like it is x86-x64 related. Using #AutoIt3Wrapper_UseX64=Y with the same script dont produce the error!

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

×