Strange Excel script error and behaviour

[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 April 17, 2018 by Juvigy
update

[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?

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

[water]

Which version of AutoIt do you run?

[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 April 17, 2018 by Juvigy
Update

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

[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

[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

 

[Juvigy]

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