Looped Array Out of Range (_Excel_RangeFind output)

[water]

22 hours ago, echoAwoo said:

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)

I didn't refer to the rest of your program, just to the Excel automation part. There is no need to automate the Excel GUI.

[echoAwoo]

So when it just crashes, no error is reported. I assume because it passes an if check it should fail? (I have an if to check for value of 1 field, at the end of the list, actually $iCount~=200 for that cycle number [could be the same daily, might not be]. May just revert back to While ending when $iCount>$iMax*2, and when it gets there it's suppose to exit the loop, instead of looking for the actual end point. But then it'll cycle for no reason (blank fields). 

Instead it predictably cycles 40 times and then crashes on the 41st with no message reported. I'll see if I can't mod the error reporting for that and coax out the answer. 



I do appreciate your help, Water. Thank you very much. 

Edited July 14, 2016 by echoAwoo

[water]

Hard to tell.
It would be great if you could strip down your script to only the Excel processing - and still showing the error. Post all needed files (ini, Excel workbook, script) so I can test it here.

[echoAwoo]

I've figured it out.

 

 

It was conditional statements failing somehow. I reworked all of my conditionals to be more succinct and it's working perfectly fine now. I've run it the last 5 nights like this and 0 crashing. 

 

Fully solved, thanks water!

Edited July 23, 2016 by echoAwoo

[water]

[echoAwoo]

<_<_<_<_<_<_<

So I solved it for a week, made no changes to the script, then it started again.

 

Cycle-n-crash is back, this time n = 60. It's failing on the include Excel.au3's function _Excel_BookOpen() at line 227, 

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

It fails at the ".Visible" part of this line with the error: 'Variable must be of type "Object".' I don't quite understand COMs yet so I don't even know why it's failing, or even why it's failing on the 60th attempt only. The functions call the books pull the data and close the books 60 times no problems (script has to pull data from multiple source documents so I can't picture a way to do it with 2 different books remaining open for each cycle with my inexperienced background. I think the fact that I'm opening and closing 2 excel documents every cycle might be a cause. 

Edited July 30, 2016 by echoAwoo
Corrected Excel book count. Both given values (4 and 3) were wrong

[water]

It's really hard to tell without seeing the full script.
Opening/Closing multiple books 60 times slooooows down your script. Maybe a redesing of your script would make it much faster and much easier to debug.
I can offer my assistance to redesign your script.

[echoAwoo]

Script execution time hasn't been an issue. Compared to me, the script is about 4000% more efficient than I am. 2-5m run time on full nights isn't something I'll complain about when it takes me almost 2 hours to do the same task manually. 

 

Let me make sure the changes I just made work then I'll post what I have.

[echoAwoo]

Alpha 3

Here's the entire script. It should be noted this is designed to work in tandem with Hilton's Hampton Inn's front desk management software, OnQ. A lot of the script is designed to cater explicitly to that program, and requires the GUI automation aspects. I've had a lot of issues with ControlSend and ControlClick not working with OnQ so that's why you see me having manually defined a lot of those actions with Send and MouseClick commands.

 

Lines 16 - 139: Report Generation. This area generates the report the rest of the script works from. It only runs if $iCount=0. (So if script crashes part-way thru and reinitialized on x cycle, it doesn't regenerate the script.)

Lines 141 - 164: Cycler. Calls the functions, increments $iCount, then repeat until $aWO[0]="Skip"

Lines 174 - 177: Logger function. 

Lines 179 - 200: Archive function. Renames the temporary log file (log.txt) into a date derived name format.

Lines 202 - 240: Work Order function. Pulls the necessary data from the Work Order book (You'll see your error reporting here!)

Lines 242 - 267: DB Compare Function. Takes the Rate Code from _at_WO(), looks in the definitions db file for the same code, returns it's corresponding tracking value. (I.E. Rate Code = Tracking Value after a "unit conversion", it's all strings so technically no metric-able units.)

Lines 269 - 447: Value Modification Function. Takes the values of the two Excel functions ( _at_WO() and _at_DB() ) and adds it to those rooms. It does not skip rooms already containing tracking values so as to ensure they are the correct values. I could put a check in elsewhere but it's pretty minimal performance hit running those unnecessary cycles. Plus it pads the logs which makes my script look more effective than it really it (heh! smoke and mirrors)

For this function I needed to call the window by titles rather than handles. I needed a way to tell exactly what screen is displayed, all of the defined titles do this, but the handle will still be the same. So if I called by handles, I'd need to do If WinGetTitle($handle) checks, which is basically the same thing as what I did just without the extra command executions.

 

If you need to see the DB and WO files I can sanitize to pull out any PII (from the WO, can't give you guest info sorry, but I'm sure you already know that and have no problem with it so this is just unnecessary text at this point)

Edited July 30, 2016 by echoAwoo

[echoAwoo]

I've considered making it so both files remain open but that would require defining a second set for $oWorkbook and right now I'd like to avoid that to avoid cross-contamination. 

 

Conjecture:

Should I just pull ALL of the data for the entire script in a single looped call into a 5d array? This would put each value (room#, name, rate code, tracking in [mostly null values here] , tracking out) into a separate dimension? (Sorry if I have this wrong, dimensional arrays greater than 2 is still new to me).

This would drastically reduce the open/close counts of excel, but makes working with the data on the code end a little harder for me. I'm willing to try it out for an a4 rework under a skeleton if you think it's a good idea.


I owe you a drink, give me the number of your favorite bar and I'll call them and prepay a tab for you

Edited July 30, 2016 by echoAwoo

[water]

I do not think you will get or need a 5D array when working with Excel. 2D is enough.
As you seem to use Excel like a database (didn't have the time to read and fully understand the whole script) you only will get a 2D array from _Excel_RangeRead.
Either use _Excel_RangeFind to retrieve the needed records. Or read the whole worksheet into memory and use _ArrayFind to search the array.

BTW: There is no need for Sleep statements after calling a _Excel* function.

[echoAwoo]

The reason I'm using Excel as a Db is because nobody else in the company knows how to use Access or even how to manage command-line dbs like Sql. Because this script is shared, I have to take into account the expertise of others when I made the decision to use Excel.  But db is the wrong word. It's more of a translation library to translate from the rate code values into the tracking code values. This document has to be manually maintained.

 

Poly-dimensional arrays larger than 3 tends to break my brain so sorry if I'm confused. 

 

Pulling 2 different 2d Arrays that are correlated would make a 4D array, right? At least it would make a 3D, I know. That's just geometry. Two 2d planes at right angles (in this instance, right angle = correlated data sets) makes a 3D volume.  IDK if it follows the basic geometry though for data-sets, that's an assumption I'm making. I can see it being possible to make it a 4d array, but like I said, arrays with dimensions larger than 3 break my brain  

 

Anyway, for the Excel call/close, you said that opening and closing the workbooks every time I need them is excessive, and I agree, especially since I'm usually only calling 2 values for the db, and 4 values for the workbook, for each cycle. Should I just run the call function once at the beginning, export that data en masse to an array of appropriate size and dimensions, and then do the same for the other document? This way that data is available on hand immediately for the script to parse?

 

Then, while making my calls for this data, do so using _ArraySearch?

 

The way it's currently being handled is it treats the wb like a 1d array by calling a specific row based on the cycle number with each column being assumed to have a particular data type. Then, I take the value from the rate-code column, compare it against the db looking for the same rate code, then moves one column to the right to get the translated tracking value. 

Edited September 1, 2016 by echoAwoo

[water]

When you say the script is shared, does this mean everyone uses their own copy of the Excel workbook or is the workbook shared as well?

[echoAwoo]

The script generates the workbook when they run it (well generates it for you by telling the software to do it), the db is shared and maintained by all (but I keep a master copy hidden somewhere in case they fsu)

Edited September 1, 2016 by echoAwoo