Looped Array Out of Range (_Excel_RangeFind output)

[echoAwoo]

SOLVED

Basically, my script needed a conditional assignment to a variable. It needed to check if the size of the array was valid before assigning any value to the variable.

 

 

So I've got a data entry task that I've been trying to automate. I actually have the script running, it cycles, draws it's necessary arrays, etc. But it will only cycle up to count #22, 23+ do not work.

THE GIST

I have two excel documents that I draw the data from. $aWO array has values from one of these documents. The instructions to draw these are under _at_wo().

This function feeds into another function to compare a derived value from the first file and request it's corresponding value. In order to do this, I used _Excel_RangeFind() (had to comment out the Cell Name and Cell Comment aspects of that function in order for it to work at all.) Then I pull the address information from array $aTracking[0][2], swap the column from A to B, and it works beautifully. 

Then cycle 23 happens, and then $aTracking[0][2] reports as out of range. Why? 

 

Here's the code. Error reported from line #70. This array call works on loops 1-22 ($iCount value 0-21)

http://pastebin.com/Bd5v6Dbc

Edited July 10, 2016 by echoAwoo

[water]

Could you please add your script to your post? Pastebin is locked here

[echoAwoo]

My apologies I only wanted to be thorough. No way of knowing if it's the function in question of something else and didn't want to paste 300 lines of code. 

 

I'll just attach it and paste the function in question. 

Func _at_db($aWO)
    Local $oWorkbook=_Excel_BookOpen($oExcel, @ScriptDir&"\db.xlsx")
    Local $aTracking=_Excel_RangeFind($oWorkbook, $aWo[3], "A:A", Default, Default, False)
    Local $sTrade=$aTracking[0][2]
    Local $sTracking=_Excel_RangeRead($oWorkbook, DEFAULT, StringReplace($sTrade, "A", "B"), 3, TRUE)
    Return $sTracking
    _Excel_BookClose($oWorkbook, FALSE)
    WinWaitClose("[CLASS:Excel]")
    $aTracking=0
EndFunc

 

alpha1.au3

[water]

You need to add some error checking. In the help file you find the values of @error for each function.

I'm sure RangeFind returns an error. And hence $aTracking is no array and crashes the script.

[echoAwoo]

During PATs my proof-of-concept worked just fine all the way to row = 10,000, why all of the sudden?

 

I've got error checks throughout the script but only for my code. I struggle with reading other people's error checks, especially when their Ifs are single line and my single-line ifs always fail. How would you suggest I begin debugging this code?

[water]

Insert something like this:

Func _at_db($aWO)
    Local $oWorkbook=_Excel_BookOpen($oExcel, @ScriptDir&"\db.xlsx")
    If @error Then MsgBox(0, "Error", "BookOpen failed with error " & @error)
    Local $aTracking=_Excel_RangeFind($oWorkbook, $aWo[3], "A:A", Default, Default, False)
    If @error Then MsgBox(0, "Error", "RangeFind failed with error " & @error)
    Local $sTrade=$aTracking[0][2]
    Local $sTracking=_Excel_RangeRead($oWorkbook, DEFAULT, StringReplace($sTrade, "A", "B"), 3, TRUE)
    If @error Then MsgBox(0, "Error", "RangeRead failed with error " & @error)
    Return $sTracking
    _Excel_BookClose($oWorkbook, FALSE)
    If @error Then MsgBox(0, "Error", "BookClose failed with error " & @error)
    WinWaitClose("[CLASS:Excel]")
    $aTracking=0
EndFunc

 

[echoAwoo]

Same error, Array $aTracking[0][2] out of range. The MsgBox with the custom errors did not generate. 

 

Is it because I'm trying to assign the array location to a variable? (That's the line the error gens from)

[echoAwoo]

Output of _ArrayDisplay on $aTracking during cycle $iCount=22

RowNumberCol 0NumberCol 1NumberCol 2NumberCol 3NumberCol 4NumberCol 5
 

 

Output of _ArrayDisplay on $aTracking during cycle 21

RowNumberCol 0NumberCol 1NumberCol 2NumberCol 3NumberCol 4NumberCol 5
[0]NumberdbNumberNumber$A$44NumberS-MFRNumberS-MFRNumber
 

 

Output of _ArrayDisplay on $aTracking during cycle 23

RowNumberCol 0NumberCol 1NumberCol 2NumberCol 3NumberCol 4NumberCol 5
[0]NumberdbNumberNumber$A$10NumberS-ARPNumberS-ARPNumber
 

 

So something about cycle 22 is directly causing it. 

 

For this particular set of instructions, cycle 22 is suppose to be skipped in my While statement (Don't initiate the functions, just $iCount += 1 [that doesn't actually work for me!, have to always $var=$var+1]

 

While $iCount <= $iMax
    ;!  0=Room  1=Last Name     2= Status   3=Rate Code     4=Tracking
    $aWO=_at_wo($iCount)
    $aDB=_at_db($aWO)
    Sleep(60)
    If $aWO[2]="INHOUSE" Then
        _at_Init($aWO, $aDB)
    EndIf
    Sleep(1000)
    $iCount=$iCount+1
    Sleep(1000)
WEnd

This while state is suppose to ask if the $aWO[2]=string "INHOUSE" if yes then execute the code bloc if no pretend bloc was executed (but actually not) and increment the counter.

 

That part doesn't actually execute, the bloc always executes. Would this be causing any interference?

[echoAwoo]

Coaxed out the error from RangeFind, it's report error 1. So for cycle 22 it's not finding the workbook? But 21 and 23 it does?

[echoAwoo]

omg I just realized you're the person who coded this function

 

<3 thanks beau for even bothering to give me any attention whatsoever

[water]

22 minutes ago, echoAwoo said:

So for cycle 22 it's not finding the workbook?

Correct. That's what @error = 1 stands for.

You will notice that there is no error code for "No records found". Means: The function always returns an array with all found records. If there re none then the array will be empty like you posted above.
Seems this information is missing from the current hlp file - will be added.

[echoAwoo]

So I need to add an if statement to catch empty arrays?

[water]

If UBound($aTracking, 1) > 0 Then ...

Checks the number of rows to be > 0.

[echoAwoo]

So this then?

If NOT UBound($aTracking, 1) > 0 Then $aTracking[0][2]=NULL

 

[echoAwoo]

Not being able to edit my posts is maddening!

 

Tanks for your input, water. I really do appreciate it. 

So if UBound($var,1) returns n rows, if it's greater than 0 that means the rangefind succeeded and script can execute normally if not I manually assign a value?

[water]

Correct.

IIRC your postcount needs to be > 10 before you can edit your posts.

[echoAwoo]

Func _at_db($aWO)
    Local $oExcel, $oWorkbook
        $oExcel=_Excel_Open(FALSE, FALSE, FALSE, FALSE, FALSE)
        $oWorkbook=_Excel_BookOpen($oExcel, @ScriptDir&"\db.xlsx")
    WinWait("Excel")
    Local $aTracking=_Excel_RangeFind($oWorkbook, $aWo[3], "A:A", Default, Default, False)
;~  _ArrayDisplay($aTracking, "Display $aTracking Array", "", DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, "")
    Local $sTracking
    If UBound($aTracking, 1) > 0 Then
        $sTracking=_Excel_RangeRead($oWorkbook, DEFAULT, StringReplace($aTracking[0][2], "A", "B"), 3, TRUE)
    Else
        $sTracking="Skip"
    EndIf
    Return $sTracking
    _Excel_BookClose($oWorkbook, FALSE)
    WinWaitClose("Excel")
    ProcessClose("Excel.exe")
    ProcessWaitClose("Excel.exe")
EndFunc

So this is what I've got now. I cleaned up the function a little bit. 

So the If UBound... Then... Else statement should catch null arrays and bypass that generated error now, right? (Sorry this is speculative editing since I can't test it until I get back to work)

 

I've got a conditional earlier on in the While statement now to catch that "Skip" assignment

Edited July 9, 2016 by echoAwoo

[water]

You are mixing GUI and COM to communicate with Excel. I suggest to get rid of the GUI automation part:

Func _at_db($aWO)
    Local $oExcel, $oWorkbook, $sTracking, $aTracking
    $oExcel = _Excel_Open(FALSE, FALSE, FALSE, FALSE, FALSE)
    $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir&"\db.xlsx")
    $aTracking = _Excel_RangeFind($oWorkbook, $aWo[3], "A:A", Default, Default, False)
    If UBound($aTracking, 1) > 0 Then
        $sTracking=_Excel_RangeRead($oWorkbook, DEFAULT, StringReplace($aTracking[0][2], "A", "B"), 3, TRUE)
    Else
        $sTracking="Skip"
    EndIf
    _Excel_BookClose($oWorkbook, FALSE)
    _Excel_Close($oExcel)
    Return $sTracking
; ==> The following statements do never get executed because the Return statement ends the function    
; ==>    _Excel_BookClose($oWorkbook, FALSE)
; ==>    WinWaitClose("Excel")
; ==>    ProcessClose("Excel.exe")
; ==>    ProcessWaitClose("Excel.exe")
EndFunc

 

[echoAwoo]

Well the script as a whole has to have GUI Automation, the actually program that I'm performing the data entry into doesn't support COMs. The entry instructions all must be done using Send commands and WinActive checks. (Pain in the *** that was to get working)

 

So we've solved the Array out of range error, thank you very much, water.

 

New issue now, it only cycles 40 times before crashing [the script]. On the 41st attempt it fails. But I can restart the script from that cycle number, and it cycle 40 times, and then crashes again. Predictably. I can start at cycle 20, and it will crash on cycle 61. Start on 30, it crashes on 71. Etc. 

 

Is this because I'm filling up memory address and not purging them when the variables aren't needed anymore? That seems unlikely as it crashes predictably like this even as x64 on a pc with 32 GB of RAM. Also, aren't the variables overwritten every time the while statement executes anew?

 

So in the mean time while I figure that out, I changed the $iCount var to an Input Box asking which cycle I'm on. 

 

Error output is 

Quote

$oExcel.Windows($oWorkbook.Name).Visible = $bVisible
$oExcel.Windows($oWorkbook.Name)^ ERROR

Variable type must be of type "Object".

 

alpha1.au3

Edited July 10, 2016 by echoAwoo
added gend error

[water]

Can you post the messages you get when the script crashes?