RichardL Posted January 18, 2017 Share Posted January 18, 2017 (edited) I was using a program that gets data from the first 2 columns of an Excel sheet (then Sends the data to another prog). Realised that it gives up after about 800 lines. I've used these methods lots of times before, never had anything like this. All the lines of code work correctly lots of times, then it fails. The data in the test sheet is the same on all lines. Started on Server 2008 with Office 2010. Moved to W7 and Office 2003, fails there as well. Both AutoIt 3.3.14.2 I've reduced the program to the minimum that fails. (Took hours, swapping things in and out trying to pin it down.) The failure is usually "Bad variable type" on the Excel read. It's better if the program does fewer Excel reads, if read column 1 only it usually reads all 985 lines. If I add writes to Excel it fails earlier. More strangeness: If I remove he StringFormat on line 97 or the StringReplace on line 90 the problem is gone. The test Excel sheet can be simple 1000 lines of 2 columns: Hello.abc, No expandcollapse popupOpt("MustDeclareVars", 1) Func _ErrFunc($oError) Local $sMsg ;ConsoleWrite( _ $sMsg = _ "err.number : " & @TAB & $oError.number & @CRLF & _ "err.windescription: " & @TAB & $oError.windescription & @CRLF & _ "err.description : " & @TAB & $oError.description & @CRLF & _ "err.source : " & @TAB & $oError.source & @CRLF & _ "err.lastdllerror : " & @TAB & $oError.lastdllerror & @CRLF & _ "err.scriptline : " & @TAB & $oError.scriptline & @CRLF & _ "err.retcode : " & @TAB & $oError.retcode & @CRLF & _ @CRLF ConsoleWrite($sMsg) MsgBox(0, "Error", $sMsg) Exit EndFunc Local $sFile_xls $sFile_xls = @ScriptDir & "\" & "R_Test3.xls" Local $sTmp Local $iRow Local $iCol Local $sMsg Local $oExcel Local $oWbook Local $oSheet Local $oErrorHandler $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") $sFile_xls = @ScriptDir & "\" & "R_Test4.xls" Local $sMmNm Local $iNofSheets Local $iSheetNo Local $sMsg If 0 Then ; (good but has been seen not to work) $oWbook = ObjGet($sFile_xls) $oExcel = $oWbook.Parent Else ; This sometimes makes 1 excel.exe process for each run $oExcel = ObjCreate("Excel.Application") If IsObj($oExcel) Then Else $sMsg = "Failed to open Excel." MsgBox(0, Default, $sMsg) Exit EndIf $oWbook = $oExcel.WorkBooks.Open($sFile_xls) If IsObj($oWbook) Then Else $sMsg = "Failed to open workbook" MsgBox(0, Default, $sMsg) Exit EndIf EndIf ;$oExcel.Visible = True Sleep(500) $oSheet = $oWbook.Worksheets("Sheet1") Local $bRqd = True Local $iCol_MimName = 1 Local $sName $oSheet = $oWbook.Worksheets(1) $iRow = 5 $bRqd = False While 1 For $iCol = 1 To 4 $sTmp = $oSheet.Cells($iRow, $iCol).Value If $iCol = 1 And $sTmp = "" Then Exit Switch $iCol Case 1 $sName = StringReplace($sTmp, ".xyz", ".ddd") Case 2 $bRqd = StringLower(StringLeft($sTmp, 1)) <> "n" EndSwitch $sMmNm = StringReplace($sName, ".ddd", "") ;$sMmNm = $sName Next ;If StringStripWS($sTmp, 8) = "" Then ExitLoop $sMsg = StringFormat("R %4d L%3d T %-20s", $iRow, StringLen($sMmNm), $sMmNm & " " & $sTmp) ConsoleWrite($sMsg & @CRLF) $sMsg = StringFormat("%4d %s\n", $iRow, $sName) ;TrayTip("Mimic", $sMsg, 1) $iRow += 1 WEnd Edited January 26, 2017 by RichardL put fix in title Link to comment Share on other sites More sharing options...
alien4u Posted January 19, 2017 Share Posted January 19, 2017 Before you do your StringReplace or StringFormat try: VarGetType() To double check if your are trying to make an String operation over a variable which for some reason is not an String. Regards Alien. Link to comment Share on other sites More sharing options...
water Posted January 19, 2017 Share Posted January 19, 2017 Why don't you use the Excel UDF that comes with AutoIt? 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...
RichardL Posted January 19, 2017 Author Share Posted January 19, 2017 (edited) @alien4u - I have the idea that AutoIt converts to string as needed, but I'll try VarGetType and force it with String(). @ Water, Because I became familiar with com+Excel using VB/VBA/VBS then started using AutoIt. Apart from the Workbook open my prog has only 1 line that uses Excel. Never had anything like this before but not sure if ever used 1000 reads. Edited January 19, 2017 by RichardL typo Link to comment Share on other sites More sharing options...
water Posted January 19, 2017 Share Posted January 19, 2017 The Excel UDF shouldn't have any problems with the size of your workbook. The UDF uses COM as well and handles COM errors fine and sets @error and @extended as described in the help file. 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...
FrancescoDiMuro Posted January 19, 2017 Share Posted January 19, 2017 @RichardL If you need some help with Excel UDF, I'd be very happy to help you Click here to see my signature: Spoiler ALWAYS GOOD TO READ: Forum Rules Forum Etiquette Link to comment Share on other sites More sharing options...
alien4u Posted January 19, 2017 Share Posted January 19, 2017 5 hours ago, RichardL said: @alien4u - I have the idea that AutoIt converts to string as needed, but I'll try VarGetType and force it with String(). @ Water, Because I became familiar with com+Excel using VB/VBA/VBS then started using AutoIt. Apart from the Workbook open my prog has only 1 line that uses Excel. Never had anything like this before but not sure if ever used 1000 reads. @RichardL Weird things happen all the time, so who knows, there is no harm in to double check the var type.In the other hand as @water point out you can use the Excel UDF which is a very good UDF developed and maintained by him. Regards Alien. Link to comment Share on other sites More sharing options...
RichardL Posted January 26, 2017 Author Share Posted January 26, 2017 Fixed. Tried the program on a WXP PC, runs perfectly. On W7 changed the compile to 64 bit and it's fine. Link to comment Share on other sites More sharing options...
water Posted January 26, 2017 Share Posted January 26, 2017 If you run the 64 bit version of Office then you need to compile the script for 64 bit as well. 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...
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