Jump to content
JohnyX

_Excel_RangeFind Error

Recommended Posts

JohnyX
Posted (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? 

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

2018-05-19_08h44_11.png

2018-05-19_08h48_07.png

2018-05-19_08h48_14.png

 

 

 

TestFile.xlsx

SourceCode.au3

2018-05-19_08h44_53.png.c0110accf0a073ebf9acb83a0e42bc0f.png

Edited by JohnyX

Share this post


Link to post
Share on other sites
water

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 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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) 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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? 

Share this post


Link to post
Share on other sites
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.
 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
JohnyX

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

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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...


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 :(

       

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 :)

  • Thanks 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
JohnyX
Posted (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 by JohnyX

Share this post


Link to post
Share on other sites
JohnyX

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

Share this post


Link to post
Share on other sites
water

Running in 64 bit mode should solve all problems. Hopefully ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×