Juvigy Posted April 17, 2018 Share Posted April 17, 2018 (edited) 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 Link to comment Share on other sites More sharing options...
Subz Posted April 17, 2018 Share Posted April 17, 2018 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? Link to comment Share on other sites More sharing options...
Juvigy Posted April 17, 2018 Author Share Posted April 17, 2018 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. Link to comment Share on other sites More sharing options...
water Posted April 17, 2018 Share Posted April 17, 2018 Which version of AutoIt do you run? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Juvigy Posted April 17, 2018 Author Share Posted April 17, 2018 (edited) 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 Link to comment Share on other sites More sharing options...
water Posted April 17, 2018 Share Posted April 17, 2018 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted April 17, 2018 Share Posted April 17, 2018 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Juvigy Posted April 17, 2018 Author Share Posted April 17, 2018 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 Link to comment Share on other sites More sharing options...
Juvigy Posted April 17, 2018 Author Share Posted April 17, 2018 Looks like it is x86-x64 related. Using #AutoIt3Wrapper_UseX64=Y with the same script dont produce the error! Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now