echoAwoo

Looped Array Out of Range (_Excel_RangeFind output)

34 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

 

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

Share this post


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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
If UBound($aTracking, 1) > 0 Then ...

Checks the number of rows to be > 0.

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Correct.

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#17 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#19 ·  Posted (edited)

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 by echoAwoo
added gend error

Share this post


Link to post
Share on other sites

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

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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

  • Similar Content

    • Simpel
      By Simpel
      Hi, I created a gui with date field but formatted as time in HH:mm. It always shows "now-time". Even if I try to set it with GUICtrlSetData.
      #include <DateTimeConstants.au3> #include <GUIConstantsEx.au3> #include <MsgBoxConstants.au3> Example() Func Example() GUICreate("My GUI get date", 200, 200, 800, 200) Local $idDate = GUICtrlCreateDate("", 20, 20, 100, 20, $DTS_TIMEFORMAT) ; to select a specific default format Local $sStyle = "HH:mm" ; Just display hours and minutes <<<<<<<<<<<<<<<<<<<<<<<<<<<< GUICtrlSendMsg($idDate, $DTM_SETFORMATW, 0, $sStyle) ConsoleWrite(GUICtrlRead($idDate) & @CRLF) GUICtrlSetData($idDate, "00:00") ConsoleWrite(GUICtrlRead($idDate) & @CRLF) GUISetState(@SW_SHOW) ; Loop until the user exits. While GUIGetMsg() <> $GUI_EVENT_CLOSE WEnd MsgBox($MB_SYSTEMMODAL, "Time", GUICtrlRead($idDate)) EndFunc ;==>Example How can I set the time field with another time and how can this field be set blank?
      Regards, Conrad
    • hunte922
      By hunte922
      Edit: The solution! Maybe?
       
      I need to download an image from a website using AutoIt but the download, when viewed in a text editor, does not appear to be the same as if I had downloaded it from a web browser.
      Could anyone help me figure out why this is happening?
      I've already tried _INetGetSource, InetGet, _IENavigate, wget, this, and a few .bat and .vbs scripts.
       
      Example Code:
      #include <Inet.au3> Local $source = _INetGetSource("http://c0.rbxcdn.com/0db36a45eae4838667ae9a46ba0c082a") Local $newfile = FileOpen(@ScriptDir & "\test.txt", 2) FileWrite($newfile, $source) FileClose($newfile) Image URL: http://c0.rbxcdn.com/0db36a45eae4838667ae9a46ba0c082a
      Screenshot attached.

    • TommyDDR
      By TommyDDR
      Hi,
      I have to set a resizing mode to differents controls in a hidden gui, that gui is initialised (hidden) and resized by a WinMove.
      But when i show it, control are not resized where thez should.
      There is a simple code that reproduce the problem.
      Same gui, same content, one displayed then moved, the other moved then displayed :
      #include <GUIConstantsEx.au3> Opt("GUIOnEventMode", 1) Opt("MustDeclareVars", 1) Global $gui[2] Global $labels[2] Global $taille[2] = [200, 100] For $i = 0 To UBound($gui, 1) - 1 $gui[$i] = GUICreate($i, $taille[0], $taille[1], $i * ($taille[0]+100) + 500, (@DesktopHeight-$taille[1])/2) GUISetOnEvent($GUI_EVENT_CLOSE, "quit", $gui) $labels[$i] = GUICtrlCreateLabel("Test resizing...", $taille[0]-105, $taille[1]-25, 100, 20) GUICtrlSetBkColor($labels[$i], 0xE0E0E0) GUICtrlSetResizing($labels[$i], BitOR($GUI_DOCKRIGHT, $GUI_DOCKBOTTOM, $GUI_DOCKWIDTH, $GUI_DOCKHEIGHT)) Next GUISetState(@SW_SHOW, $gui[0]) For $i = 0 To UBound($gui, 1) - 1 WinMove($gui[$i], "", Default, Default, $taille[0]+100, $taille[1]+100) Next GUISetState(@SW_SHOW, $gui[1]) While(True) Sleep(10) WEnd Func quit() Exit EndFunc Is that a bug or do miss i something ?
      EDIT : This bug disapear if gui is shown at lease one time (even if hide then)
    • Simpel
      By Simpel
      Hi,
      I updated to the last version. Since then a lot of functions are not working.  For instance expanding abbreviation. My au3abbrev.properties are correct and abbrev.properties are including both import lines. The abbreviation is red but won't expand. Neither with space nor Ctrl+B.
      Debug to msgbox and console not working, list functions, jump to function, open include too.
      SciTE Jump works. Block and box comment too.
      I have the feeling all functions especially for autoit are not working.
      Any help? Regards, Conrad
    • Simpel
      By Simpel
      Hi there.
      I'm testing this code:
      #include "WinHttp.au3" ; http://www.w3schools.com/php/demo_form_validation_escapechar.php $sUserName = "SomeUserName" $sEmail = "some.email@something.com" $sDomain = "www.w3schools.com" $sPage = "/php/demo_form_validation_escapechar.php" ; Data to send $sAdditionalData = "name=" & $sUserName & "&email=" & $sEmail ; Initialize and get session handle $hOpen = _WinHttpOpen("Mozilla/5.0 (Windows NT 6.1; WOW64; rv:40.0) Gecko/20100101 Firefox/40.0") ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $hOpen = ' & $hOpen & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console ; Get connection handle $hConnect = _WinHttpConnect($hOpen, $sDomain) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $hConnect = ' & $hConnect & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console ; Make a request $hRequest = _WinHttpOpenRequest($hConnect, "POST", $sPage) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $hRequest = ' & $hRequest & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console ; Send it. Specify additional data to send too. This is required by the Google API: $hSendRequest = _WinHttpSendRequest($hRequest, "Content-Type: application/x-www-form-urlencoded", $sAdditionalData) ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $hSendRequest = ' & $hSendRequest & @CRLF & '>Error code: ' & @error & @TAB & "@extended: " & @extended & @CRLF) ;### Debug Console ; Wait for the response $hReceiveResponse = _WinHttpReceiveResponse($hRequest) ; See what's returned Dim $sReturned If _WinHttpQueryDataAvailable($hRequest) Then ; if there is data Do $sReturned &= _WinHttpReadData($hRequest) Until @error EndIf ; Close handles _WinHttpCloseHandle($hRequest) _WinHttpCloseHandle($hConnect) _WinHttpCloseHandle($hOpen) ; See what's returned ConsoleWrite("Returned: " & $sReturned & @CRLF) But sending a request fails.
      Console outputs this:
      >Running:(3.3.12.0) ... --> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop @@ Debug(14) : $hOpen = 0x00000000007DF540 >Error code: 0 @@ Debug(18) : $hConnect = 0x00000000008271C0 >Error code: 0 @@ Debug(22) : $hRequest = 0x00000000008B88E0 >Error code: 0 @@ Debug(26) : $hSendRequest = 0 >Error code: 1 @extended: 0 Returned: +>17:00:18 AutoIt3.exe ended.rc:0 +>17:00:18 AutoIt3Wrapper Finished. >Exit code: 0    Time: 0.6003 I'm testing it at work. There, we always have to identify at a proxy to be allowed to go to internet. But requests via inet.au3 will work. Maybe our firewall will block the request?
      Any ideas? I don't have any possibilities to test at home, because of OSX.
       
      Regards, Conrad