_Excel_RangeFind loop call crashs random

sksbir · February 13, 2018

Hi

I'm trying to use _Excel_RangeFind with autoit v3.3.14.3

_Excel_RangeFind is called in a loop. The first search works, the next don't find anything ( and not due to keyword which don't exists ) . After between 4 and 9 loops ( so far), I get @error=4, @extended=1

I created a test2.xlsx sheet, and and test2.au3

Here is code for test2.au3:

Spoiler

#include <Excel.au3>
        #include <MsgBoxConstants.au3>
        #include <Array.au3> ; déjà inclu dans excel.au3.             
            
        $NBSERVEURS=0
        While $NBSERVEURS<3
                $BIDON=MsgBox($MB_OK,"Validation du presse papier","Le presse papier doit contenir la liste des serveurs, 1 serveur par ligne, serveur en début de ligne, seul sur la ligne ou suivi par un espace")
                $LSTSERVEUR=ClipGet()
                $LSTTABSERVEUR=StringSplit($LSTSERVEUR,@CR)
        ;; _ArrayDisplay($LSTTABSERVEUR)
                $NBSERVEURS=$LSTTABSERVEUR[0]
                for $CPT=1 To $NBSERVEURS
                    $BIDON=StringSplit($LSTTABSERVEUR[$CPT]," ")
                    $LSTTABSERVEUR[$CPT]=$BIDON[1]
                Next
        ;; _ArrayDisplay($LSTTABSERVEUR)
        WEnd                
            ; Create application object and open workbook
        Local $oExcel = _Excel_Open()
        If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test2.xlsx")
        If @error Then
            MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error opening workbook" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
            _Excel_Close($oExcel)
            Exit
        EndIf               
            ; *****************************************************************************
        ; Attach to the first Workbook where the file name matches
        ; *****************************************************************************
        Local $sWorkbook = "test2.xlsx"
        $oWorkbook = _Excel_BookAttach($sWorkbook, "filename")
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Error attaching to '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        ;; MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Search by 'filename':" & @CRLF & @CRLF & "Successfully attached to Workbook '" & $sWorkbook & "'." & @CRLF & @CRLF & "Value of cell A2: " & $oWorkbook.Activesheet.Range("A2").Value)             
            
        ;; $oWorkbook.sheets("Entrees").Activate ;
        $oWorkbook.sheets("XXX").Activate ;             
            
        for $CPT=1 To $NBSERVEURS
            if $LSTTABSERVEUR[$CPT] <> "" then
                MsgBox(0,"Test","recherche:" & $LSTTABSERVEUR[$CPT])
                ;;     $aResult = _Excel_RangeFind($oWorkbook, "*" & $LSTTABSERVEUR[$CPT] & "*", "H1:H99999",Default,default,$xlWhole )
                $aResult = _Excel_RangeFind($oWorkbook, $LSTTABSERVEUR[$CPT] )              
                    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
                ; MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.")
                _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")
                $aResult=""
            EndIf
        Next                
            exit

[edit] Added excel sheet.

Has test, you must copy data in your clipboard.here is a sample:

Spoiler

AZESRVSIG00029
AZESRVSIG00027
AZESRVSIG00028
AZESRVSIG00030
AZESRVSIG00031
AZESRVOOL00001 qsdqsdqsd
AZESRVOOL00002
AZESRVOOL00003
AZESRVDGG20001
AZESRVDGG20002 xxxx
AZESRVDGG30001
AZESRVDGG30002
AZESRVCMT30003
AZESRVCMT30004
AZESRVNIR30002  qsdqsdqsd
AZESRVNIR20002
AZESRVNEO30242
AZESRVNEO30243
AZESRVNEO30244
AZESRVNEO30245
AZESRVNEO30246  sfsdfs
AZESRVNEO30247
AZESRVNEO30248
AZESRVNEO30249
AZESRVNEO30250
AZESRVNEO30251
AZESRVNEO30252

test2.xlsx

Edited February 15, 2018 by sksbir

water · February 13, 2018

Can you post the test.xlsx as well so I can play with it and your script?

sksbir · February 13, 2018

So I did in first post.

1/ copy the whole sample in clipboard , launch : you will find AZESRVSIG00029, but not the next : AZESRVSIG00027 ... break or go until crash.

2/ exclude the first from selection, copy in clipboard, launch : you will find AZESRVSIG00027....

Edited February 13, 2018 by sksbir

water · February 13, 2018

I have stripped down your example to the bare necessities and it works just fine running AutoIt 3.3.14.3.

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $LSTTABSERVEUR[] = [3, "AZESRVSIG00029", "AZESRVSIG00027", "AZESRVSIG00028"]

; Create application object and open workbook
Local $oExcel = _Excel_Open()
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test2.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error opening workbook" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; No need to attach to the workbook as the workbook object is returned by _Excel_BookOpen

$oWorkbook.sheets("XXX").Activate ;

For $CPT = 1 To $LSTTABSERVEUR[0]
    If $LSTTABSERVEUR[$CPT] <> "" Then
        MsgBox(0, "Test", "recherche: " & $LSTTABSERVEUR[$CPT])
        ;;     $aResult = _Excel_RangeFind($oWorkbook, "*" & $LSTTABSERVEUR[$CPT] & "*", "H1:H99999",Default,default,$xlWhole )
        $aResult = _Excel_RangeFind($oWorkbook, $LSTTABSERVEUR[$CPT])
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        ; MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.")
        _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")
        $aResult = ""
    EndIf
Next
Exit

How does it work for you?

sksbir · February 13, 2018

ok

There were here two different problems, and one is now solved.

1st failure was here and this explains why I got a result just for 1st item of the array: other items were prefixed with @LF character.

$LSTTABSERVEUR=StringSplit($LSTSERVEUR,@CR)

And correction is

$LSTTABSERVEUR=StringSplit($LSTSERVEUR,@CRLF,$STR_ENTIRESPLIT)

The other problem is still here : script crashs at 8st or 9st loop

In your code, change array init with this to meet the problem

Local $LSTTABSERVEUR[] = [12, "AZESRVSIG00030", "AZESRVSIG00031", "AZESRVOOL00001", "AZESRVOOL00002", "AZESRVOOL00003", "AZESRVDGG20001", "AZESRVSIG00030", "AZESRVSIG00031", "AZESRVOOL00001", "AZESRVOOL00002", "AZESRVOOL00003", "AZESRVDGG20001"]

Edited February 13, 2018 by sksbir

water · February 14, 2018

The following script (I just changed the array you provided) works without problems:

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $LSTTABSERVEUR[] = [12, "AZESRVSIG00030", "AZESRVSIG00031", "AZESRVOOL00001", "AZESRVOOL00002", "AZESRVOOL00003", "AZESRVDGG20001", "AZESRVSIG00030", "AZESRVSIG00031", "AZESRVOOL00001", "AZESRVOOL00002", "AZESRVOOL00003", "AZESRVDGG20001"]

; Create application object and open workbook
Local $oExcel = _Excel_Open()
If @error <> 0 Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test2.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example", "Error opening workbook" & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; No need to attach to the workbook as the workbook object is returned by _Excel_BookOpen

$oWorkbook.sheets("XXX").Activate ;

For $CPT = 1 To $LSTTABSERVEUR[0]
    If $LSTTABSERVEUR[$CPT] <> "" Then
        MsgBox(0, "Test", "recherche: " & $LSTTABSERVEUR[$CPT])
        ;;     $aResult = _Excel_RangeFind($oWorkbook, "*" & $LSTTABSERVEUR[$CPT] & "*", "H1:H99999",Default,default,$xlWhole )
        $aResult = _Excel_RangeFind($oWorkbook, $LSTTABSERVEUR[$CPT])
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        ; MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.")
        _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")
        $aResult = ""
    EndIf
Next
Exit

sksbir · February 14, 2018

I was afraid that you answer this. I copy-paste the code in your last reply and run it ( just to be sure I EXACTLY run the same ) : still @error=4, @extended=1 after random loops, mostly at 10th search.

I'm using office 2010 / windows 7 32 bits ( Office laptop ) . I suspect some memory leak problem.

May you change the end of test script ?

for $BCL1= 1 to 100
For $CPT = 1 To $LSTTABSERVEUR[0]
    If $LSTTABSERVEUR[$CPT] <> "" Then
        $aResult = _Excel_RangeFind($oWorkbook, $LSTTABSERVEUR[$CPT])
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "_Excel_RangeFind/" & $BCL1 & "," & $CPT , "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    EndIf
Next
Next

By me, It crashes at 39,5 , 39,4 ... not 1,9 has expected... just because I removed _arraydisplay from loop ? ... strange strange....another clue would be that a timeout is at work somewhere.--> edit . no. with a sleep(5) or sleep(10) or sleep(100) added in loop, still crashing at 39,5

[edit] I re-run a series of runs, and it's always 39,6 until now.

[edit] with _ArrayDisplay($aResult) in loop, crash at 2,12...

Edited February 14, 2018 by sksbir

water · February 14, 2018

I have been playing high and low, even recreated the Excel workbook. But I was unable to find the root cause of this problem.
Somehow the Workbook object becomes invalid.

I'm not sure Excel is made for this kind of access - or it might be a bug in Excel as well?
Did you have a look at the wiki and ADO to access Excel like a database?

sksbir · February 15, 2018

water, did you achieve to reproduce the problem at yours , or I'm alone to have this problem ?

water · February 15, 2018

I was able to reproduce the problem with Excel 2017 on Windows 10 with AutoIt 3.3.14.3.

sksbir · February 15, 2018

and how far did it works ? did your counters approach mine or not ?

water · February 15, 2018

Depending on how I changed the script it was somewhere between 30 and 75.
That's why I assume it might be caused by an Excel bug.

Sign In

_Excel_RangeFind loop call crashs random

Recommended Posts

sksbir

water

sksbir

water

sksbir

water

sksbir

water

sksbir

water

sksbir

water

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta