JohnyX Posted May 19, 2018 Share Posted May 19, 2018 (edited) Hello, I am having troubles with _Excel_RangeFind function. I am trying to get the random strings bellow some cells with fixed values ( some kind of headers) from multiple files, but the script always crashes after about 100 files. Can anyone tell me what am I doing wrong or suggest a workaround? expandcollapse popup#include <StaticConstants.au3> #include <WindowsConstants.au3> #include <Excel.au3> #include <Array.au3> #include <EditConstants.au3> Func Form1Close() Exit 0 EndFunc ;==>Form1Close Global $oShape Local $folder = "C:\Users\John\Desktop\" RecursiveFileSearch($folder) Func RecursiveFileSearch($startDir, $depth = 0) If $depth = 0 Then Global $RFSstring = "" $search = FileFindFirstFile($startDir & "\*.*") If @error Then Return Local $oExcel = _Excel_Open(True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_Open Error", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) While 1 $next = FileFindNextFile($search) If @error Then ExitLoop If StringInStr(FileGetAttrib($startDir & "\" & $next), "D") Then RecursiveFileSearch($startDir & "\" & $next, $depth + 1) ElseIf StringInStr($startDir & "\" & $next, ".xls") Or StringInStr($startDir & "\" & $next, ".xlsx") Or StringInStr($startDir & "\" & $next, ".csv") Then Local $sWorkbook = $startDir & "\" & $next Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_BookOpen Error", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $aResult1 = _Excel_RangeFind($oWorkbook, "Fixed Text1") If @error Then MsgBox($MB_SYSTEMMODAL, "_Excel_RangeFind Error 1", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $aResult2 = _Excel_RangeFind($oWorkbook, "Fixed Text2") If @error Then MsgBox($MB_SYSTEMMODAL, "_Excel_RangeFind Error 2", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $aResult3 = _Excel_RangeFind($oWorkbook, "Fixed Text3") If @error Then MsgBox($MB_SYSTEMMODAL, "_Excel_RangeFind Error 3", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) $var1 = $aResult1[0][2] ; Get the Cell where the fixed text is located Local $sResult = _Excel_RangeRead($oWorkbook, Default, StringReplace($var1, StringRight($var1, 2), StringRight($var1, 2)+1)) ; Get the cell's bellow value where the random string is located If @error Then MsgBox(0, "Range Read Error 1", "returned @error = " & @error) FileWriteLine(@ScriptDir & "\log.txt", "1: "& $sResult) $var2 = $aResult2[0][2] ; Get the Cell where the fixed text is located Local $sResult = _Excel_RangeRead($oWorkbook, Default, StringReplace($var2, StringRight($var2, 2), StringRight($var2, 2)+1)) ; Get the cell's bellow value where the random string is located If @error Then MsgBox(0, "Range Read Error 2", "returned @error = " & @error) FileWriteLine(@ScriptDir & "\log.txt", "2: "& $sResult) $var3 = $aResult3[0][2] ; Get the Cell where the fixed text is located Local $sResult = _Excel_RangeRead($oWorkbook, Default, StringReplace($var3, StringRight($var3, 2), StringRight($var3, 2)+1)) ; Get the cell's bellow value where the random string is located If @error Then MsgBox(0, "Range Read Error 3", "returned @error = " & @error) FileWriteLine(@ScriptDir & "\log.txt", "3: "& $sResult) ; Write the random text found FileWriteLine(@DesktopDir & "\Files Processed.txt", $startDir & "\" & $next) ; Write all file processed _Excel_BookClose($oWorkbook, False) EndIf WEnd _Excel_Close($oExcel, False, True) FileClose($search) If $depth = 0 Then Return StringSplit(StringTrimRight($RFSstring, 1), "*") EndFunc ;==>RecursiveFileSearch I am using Windows 7 Pro x64 , Office 2013 and AutoIt v3.3.14.5 Thank you. TestFile.xlsx SourceCode.au3 Edited May 23, 2018 by JohnyX Link to comment Share on other sites More sharing options...
water Posted May 19, 2018 Share Posted May 19, 2018 What kind of file does cause the script to crash? xls, xlsx or csv? Does it always crash whn processing the same 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...
JohnyX Posted May 19, 2018 Author Share Posted May 19, 2018 Hi water, I only have xls and xlsx files, the error occurs randomly every time with different files. I noticed that if i use the script as it is right now, I can only process about 100 files. If I simplify it and only check for one value instead of 3, I can process more the 250 files until the error occurs. I can't find a logical reason for this. If I only use _Excel_RangeRead with a defined range (A1) it crashes without any errors when reaching about 500 files. Is there another way to do this job without the use of this functions? (Like we used $oShapes instead of _Excel_PictureAdd) Link to comment Share on other sites More sharing options...
water Posted May 19, 2018 Share Posted May 19, 2018 The Excel UDF is just a wrapper for Excel COM. So this won't help. Another idea: Add a Sleep(5000) after _Excel_BookClose. Adding a Sleep statement has helped in a few cases. Seems to be a problem with Excel itself. 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...
JohnyX Posted May 19, 2018 Author Share Posted May 19, 2018 This is the simplest version I could think of, of the script I need. Still, the script crashes before reaching 30 files. I will uninstall AutoIt and download a fresh copy. Hopefully it will solve the problem. #include <Array.au3> #include <File.au3> #include <Excel.au3> Local $Dir = "C:\Users\John\Desktop\test\" $aArray = _FileListToArrayRec($Dir, "*.xls", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_SORT, $FLTAR_RELPATH) Local $oExcel = _Excel_Open(True) For $i = 1 To UBound($aArray, $UBOUND_ROWS) -1 Step +1 Local $sWorkbook = $Dir & $aArray[$i] Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A11") ; Get the cell's bellow value where the random string is located If @error Then MsgBox(0, "Range Read Error 1", "@error = " & @error & ", @extended = " & @extended) FileWriteLine(@ScriptDir & "\log.txt", $sResult) Next _ArrayDisplay($aArray, "No 'Include' folder") ArrayRec.au3 Link to comment Share on other sites More sharing options...
JohnyX Posted May 19, 2018 Author Share Posted May 19, 2018 3 minutes ago, water said: The Excel UDF is just a wrapper for Excel COM. So this won't help. Another idea: Add a Sleep(5000) after _Excel_BookClose. Adding a Sleep statement has helped in a few cases. Seems to be a problem with Excel itself. Thank you. I will try that. Link to comment Share on other sites More sharing options...
water Posted May 19, 2018 Share Posted May 19, 2018 I do not think it is caused by AutoIt. Office 2013 is quite old, mainstream support ended on April 10, 2018. Do you run Office 2013 with SP 1? 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...
JohnyX Posted May 19, 2018 Author Share Posted May 19, 2018 All our desktops comes with pre-installed Windows 7 Professional and Office 2013. So it's nothing to do about that, I will also try to run the script on another OS / Office version to see if it works. Do you get any error when running my second script? Link to comment Share on other sites More sharing options...
water Posted May 19, 2018 Share Posted May 19, 2018 Can't test at the moment - there is no Office on my Linux machine Will test as soon as possible on my Windows 7, Office 2016 computer. 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...
JohnyX Posted May 19, 2018 Author Share Posted May 19, 2018 Thanks a lot, waiting for feedback, in the meantime I'll try to find a workaround. Link to comment Share on other sites More sharing options...
water Posted May 19, 2018 Share Posted May 19, 2018 I just tested. It crashes here as well. Don't know why 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 May 19, 2018 Share Posted May 19, 2018 Seems I found a way to make it work Set parameter 4 and 5 of _Excel_RangeRead like: $sResult = _Excel_RangeRead($oWorkbook, Default, "A1", 1, True) Seems there is a problem with the Excel transpose function... 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...
JohnyX Posted May 19, 2018 Author Share Posted May 19, 2018 I am going to test it right away, also I am going to set those parameters to _Excel_RangeFind function to the first script. Maybe it will fix it too. Link to comment Share on other sites More sharing options...
water Posted May 19, 2018 Share Posted May 19, 2018 Unfortunately it doesn't Without setting the parameters it crashed after 546 files, with the parameters set it crashes after 842 files. The event log shows a problem with OLEAUT32.dll. Don't know what happens here 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 May 19, 2018 Share Posted May 19, 2018 Seems I solved the problem. Add the following line to your script to run it in 64bit mode: #AutoIt3Wrapper_UseX64=y Now it processes 2500 files without problem JohnyX 1 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...
JohnyX Posted May 19, 2018 Author Share Posted May 19, 2018 (edited) Ok, you are too fast , I was still running your first suggestion. Let me try this too.. So the problem seems to be related to the software architecture, but will it fix all excel UDFs? Or just -_Excel_RangeRead? Edited May 19, 2018 by JohnyX Link to comment Share on other sites More sharing options...
JohnyX Posted May 19, 2018 Author Share Posted May 19, 2018 Thank you very much for your support, just reached 1600 and still counting... Link to comment Share on other sites More sharing options...
water Posted May 19, 2018 Share Posted May 19, 2018 Running in 64 bit mode should solve all problems. Hopefully 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...
JohnyX Posted May 21, 2018 Author Share Posted May 21, 2018 On 5/19/2018 at 9:01 PM, water said: Seems I solved the problem. Add the following line to your script to run it in 64bit mode: #AutoIt3Wrapper_UseX64=y Now it processes 2500 files without problem For the record, using this line on top of any script, will make the scrip work and prevent the crash, only if using Scite4Autoit3. It will not work using the default included version of Scite in Autoit bundle. Link to comment Share on other sites More sharing options...
water Posted May 21, 2018 Share Posted May 21, 2018 Thanks for the feedback! I've already added this to the Excel wiki 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