# Looped Array Out of Range (_Excel_RangeFind output)

## Recommended Posts

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)

Edited by echoAwoo

##### Share on other sites

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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:

##### 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 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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX (2018-01-27 - Version 1.3.3.1) - 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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX (2018-01-27 - Version 1.3.3.1) - 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 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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX (2018-01-27 - Version 1.3.3.1) - 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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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:

##### Share on other sites

So this then?

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

##### 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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX (2018-01-27 - Version 1.3.3.1) - 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 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 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 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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:

##### Share on other sites

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

##### Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - 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:

## 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

• ### Similar Content

• By CodingMonkey81
All,
I'm working on automating the update process for a piece of software that we use at my company. The installer file always contains the version number as a part of the file name (eg InstallFile_9342.msi) and the numbers always change with every update.
How do I use AutoIt to run the program? I know I can't use a wildcard in place of the numbers when using the "Run" command.
I tried
Local $aArray = _FileListToArray("C:\path\to\file", "InstallFile_*.msi", 0, 1) Run("$aArray") and I also tried
Local $bInstall = FileFindFirstFile("InstallFile_*.msi") Run("$bInstall") and neither worked.
Can someone please point me in the right direction? I've done some searching on the forum and haven't found anyone in a similar situation before.
Thanks!
• By RHolmes
I have a program that has a control that changes color a few seconds into running. So ideally, I would poll this to tell when an event has occurred.
I can't seem to retrieve the correct color value for a control. It always seems to return white indicating that its selecting somewhere else in the window.
In the PixelGetColor call I'm adding half the width to the x value and subtracting half the height to the y value  in order to get the center of the control. (assuming the coords returned by ControlGetPos are top left - which i can't be sure of) But I've also tried without modifying the x/y and with changing the PixelCoordMode option to 2. Maybe I'm making a silly mistake and can't see it? Any help would be appreciated.
Code is below:
Opt("PixelCoordMode", 0)
FileChangeDir( "C:\Where\My\File\Is" );
Run( "MyProgram.exe" )
Local $hClient = WinWaitActive($CLIENT_TITLE, "", 10 )
Local $systemIndicatorClassNN= "[CLASS:Qt5QWindowIcon; INSTANCE:99]" Local$hSystemIndicator = ControlGetHandle ( $hClient, "",$systemIndicatorClassNN)
Local $xywh = ControlGetPos ($hClient, "", $hSystemIndicator ) For$i = 10 To 1 Step -1
$color = PixelGetColor ($xywh[0] + ($xywh[2]/2),$xywh[1] - ($xywh[3]/2),$hClient )
LogToFile( $color ) Sleep( 2000 ) Next • By VAN0 Hello. I have a program XX that asks for user confirmation on exit, which hangs the system reboot/shutdown process until I confirm. So I decided create a autoit script that would automatically close that dialog and let the system reboot/shutdown. The problem I'm facing is that my scripts now exits before the XX. Is there a way delay script exit until the XX is closed but only when system is rebooting/shutting down? Thank you. • 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 • 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.

×

• Wiki

• Back

• Git