Jump to content

_Excel_RangeFind Error


Recommended Posts

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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 :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...