Jump to content
sksbir

_Excel_RangeFind loop call crashs random

Recommended Posts

sksbir

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 by sksbir

Share this post


Link to post
Share on other sites
water

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


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
sksbir

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 by sksbir

Share this post


Link to post
Share on other sites
water

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?


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
sksbir

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 by sksbir

Share this post


Link to post
Share on other sites
water

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

 


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
sksbir

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 by sksbir

Share this post


Link to post
Share on other sites
water

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?
 


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
sksbir

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

Share this post


Link to post
Share on other sites
water

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


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
sksbir

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

Share this post


Link to post
Share on other sites
water

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.


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

×