_Excel_RangeFind Error

[JohnyX]

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

[water]

What kind of file does cause the script to crash? xls, xlsx or csv?
Does it always crash whn processing the same file?

[JohnyX]

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) 

[water]

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.

[JohnyX]

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

[JohnyX]

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. 

[water]

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?

[JohnyX]

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? 

[water]

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.
 

[JohnyX]

Thanks a lot, waiting for feedback, in the meantime I'll try to find a workaround. 

[water]

I just tested. It crashes here as well. Don't know why

[water]

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

[JohnyX]

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.

[water]

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

[water]

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]

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

[JohnyX]

Thank you very much for your support, just reached 1600 and still counting...

[water]

Running in 64 bit mode should solve all problems. Hopefully

[JohnyX]

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. 

[water]

Thanks for the feedback! I've already added this to the Excel wiki