treefingers Posted August 16, 2007 Share Posted August 16, 2007 Hello, I have a script that I'm working on, and have had previous help before on, and would like some more help on . First off, here's what I have: CODEMsgBox(64, "Notice","Navigate to the Completed Order Re-Print Screen in STORIS Before Continuing") $fileVariableName = FileOpen("invoices.txt", 0) If $fileVariableName = -1 Then MsgBox(0,"Failure","Failed to open file") Exit EndIf While 1 For $x = 1 to 50 $lineVariableName = FileReadLine($fileVariableName) If @error = -1 Then ExitLoop 2 WinActivate("Completed Order Re-Print") Send($lineVariableName) Sleep(500) Send("{ENTER}") Sleep(500) Send("!a") Sleep(1000) Next Send("!r") Sleep(3500) Send("{SPACE}") Sleep(16000) Send("{Space}") Sleep(10000) Send("COR") Send("{ENTER}") Sleep(5000) Send("{TAB}") WEnd FileClose($fileVariableName) Basically what this does is open our POS (Point of sale, but yes, the other acronym is correct) software, and input a bunch of tax exempt invoices to reprint. Our software actually creates an Excel document containing all the invoice numbers that need to be reprinted. What I have been doing is opening this document, copying the first column, pasting to "invoices.txt", then saving the file, closing excel, and running the script. What I would like to do is have autoit read directly from the excel document. I looked through the ExcelCOM_UDF, and found the _ExcelReadArray Function, which I imagine is a good place to start. What I'm not sure I understand, is how to modify the function to read an unknown number of entries. There could be 40 invoices (rows) or 500. I need it to read how ever many are there, save it to an array, then feed the array into the script that does this tedious work. Any advice? Thanks! Link to comment Share on other sites More sharing options...
GMK Posted August 16, 2007 Share Posted August 16, 2007 Assuming $oExcel is your Excel object, you can find the number of rows used using COM in the following manner:$LastRow = $oExcel.ActiveSheet.UsedRange.Rows.Count Perhaps that should get you started, but let me know if I can be of further assistance. Link to comment Share on other sites More sharing options...
weaponx Posted August 16, 2007 Share Posted August 16, 2007 You could try this: #include "ExcelCOM_UDF.au3" #include $Obj = _ExcelBookOpen(@ScriptDir & "\test.xls", 0, False) For $X = 1 to $Obj.ActiveSheet.UsedRange.Rows.Count ;Object,StartRow,StartColumn,NumCells,Direction,Index $ARRAY = _ExcelReadArray($Obj, $X, 1, 2, 0, 0) _ArrayDisplay ($ARRAY) Next _ExcelBookClose($Obj) My xls has only 2 columns: 23525245 Bob 32423252 Steve 24532534 George etc... Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 16, 2007 Share Posted August 16, 2007 Hello, I have a script that I'm working on, and have had previous help before on, and would like some more help on . First off, here's what I have: Basically what this does is open our POS (Point of sale, but yes, the other acronym is correct) software, and input a bunch of tax exempt invoices to reprint. Our software actually creates an Excel document containing all the invoice numbers that need to be reprinted. What I have been doing is opening this document, copying the first column, pasting to "invoices.txt", then saving the file, closing excel, and running the script. What I would like to do is have autoit read directly from the excel document. I looked through the ExcelCOM_UDF, and found the _ExcelReadArray Function, which I imagine is a good place to start. What I'm not sure I understand, is how to modify the function to read an unknown number of entries. There could be 40 invoices (rows) or 500. I need it to read how ever many are there, save it to an array, then feed the array into the script that does this tedious work. Any advice? Thanks! This works as example of using the ExcelCOM_UDF functions to read one entire column ('B' in the example) to an array. Others have posted examples using the object references more directly, this uses only the _Excel* functions and has lots of error reporting added for help in debugging: #include <array.au3> #include <ExcelCOM_UDF.au3> $sXLS = @ScriptDir & "\Test2.xls" ; Open Excel book $oExcel = _ExcelBookOpen ($sXLS) If Not @error Then $avExtent = _ExcelSheetUsedRangeGet ($oExcel, 1) If Not @error Then $avColData = _ExcelReadArray ($oExcel, 1, 2, $avExtent[3], 1, 1) If Not @error Then _ArrayDisplay($avColData, "Debug: $avColData") Else ConsoleWrite("Debug: Failed to read column, @error = " & @error & @LF) EndIf Else ConsoleWrite("Debug: Failed to get extent, @error = " & @error & @LF) EndIf Else ConsoleWrite("Debug: Failed to open Excel book: " & $sXLS & @LF) EndIf Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
treefingers Posted August 17, 2007 Author Share Posted August 17, 2007 (edited) Okay, here's what I've got so far, thanks to all the help: #include <array.au3> #include <ExcelCOM_UDF.au3> $sXLS = @ScriptDir & "\TaxExempt.xls" ; Open Excel book $oExcel = _ExcelBookOpen ($sXLS) $avExtent = _ExcelSheetUsedRangeGet ($oExcel, 1) $avColData = _ExcelReadArray ($oExcel, 2, 1, $avExtent[3], 1, 1) While 1 For $xx = 1 To Ubound($avColData) - 1 For $x = 1 to 50 WinActivate("Completed Order Re-Print") Send($avColData[$xx]) Sleep(500) Send("{ENTER}") Sleep(500) Send("!a") Sleep(1000) Next Send("!r") Sleep(3500) Send("{SPACE}") Sleep(16000) Send("{Space}") Sleep(10000) Send("COR") Send("{ENTER}") Sleep(5000) Send("{TAB}") WEnd It is working, however, it is only inputting the first line of the array, 50 times. How to I make it sequentially read the array column, in sets of 50? Edited August 17, 2007 by treefingers Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 17, 2007 Share Posted August 17, 2007 (edited) Okay, here's what I've got so far, thanks to all the help: While 1 For $xx = 1 To Ubound($avColData) - 1 For $x = 1 to 50 ; Next WEnd It is working, however, it is only inputting the first line of the array, 50 times. How to I make it sequentially read the array column, in sets of 50? You are showing two For's and only one Next, all inside a While/WEnd loop. What was the plan with those nested loop? Edited August 17, 2007 by PsaltyDS Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
treefingers Posted August 17, 2007 Author Share Posted August 17, 2007 You are showing two For's and only one Next, all inside a While/WEnd loop. What was the plan with those nested loop? Yeah, now i see the lack of the second next.. Forgive my lack of programming skills- really, i'm not entirely sure. I need the array to be 'sent' sequentially, in increments of 50. Our POS software crashes if i try to print more than 50 documents at a time. Let me try and do kind of a step by step. #include <array.au3> #include <ExcelCOM_UDF.au3> $sXLS = @ScriptDir & "\TaxExempt.xls" ; Open Excel book $oExcel = _ExcelBookOpen ($sXLS) $avExtent = _ExcelSheetUsedRangeGet ($oExcel, 1) $avColData = _ExcelReadArray ($oExcel, 2, 1, $avExtent[3], 1, 1)oÝ÷ Ù8b²Z(¦wvËhmçh觱ël¡þtjYmì!W¬z«Ø«y§ba{Mújð¨6iÖi«¢+ÙM¹ ÅÕ½ÐìÌÌíÈÅÕ½Ðì¤)M±À ÌÔÀÀ¤)M¹ ÅÕ½ÐííMA ôÅÕ½Ðì¤)M±À ÄØÀÀÀ¤)M¹ ÅÕ½ÐííMÁôÅÕ½Ðì¤)M±À ÄÀÀÀÀ¤)M¹ ÅÕ½Ðí =HÅÕ½Ðì¤)M¹ ÅÕ½Ðíí9QIôÅÕ½Ðì¤)M±À ÔÀÀÀ¤)M¹ ÅÕ½ÐííQ ôÅÕ½Ðì After every 50 orders, that process needs to be run (which actually prints the set of 50). I'm not really sure how to loop all that.. so I kind of.. guessed. Heh. Link to comment Share on other sites More sharing options...
PsaltyDS Posted August 17, 2007 Share Posted August 17, 2007 OK, that makes sense... expandcollapse popup#include <array.au3> #include <ExcelCOM_UDF.au3> $sXLS = @ScriptDir & "\TaxExempt.xls" ; Open Excel book $oExcel = _ExcelBookOpen ($sXLS) $avExtent = _ExcelSheetUsedRangeGet ($oExcel, 1) $avColData = _ExcelReadArray ($oExcel, 2, 1, $avExtent[3], 1, 1) $iCnt = 0 For $x = 1 To UBound($avColData) - 1 $iCnt += 1 WinActivate("Completed Order Re-Print") Send($avColData[$x]) Sleep(500) Send("{ENTER}") Sleep(500) Send("!a") Sleep(1000) If $iCnt = 50 Then _RePrint() $iCnt = 0 EndIf Next If $iCnt Then _RePrint() ; ----------------------- ; Function _RePrint() ; ----------------------- Func _RePrint() Send("!r") Sleep(3500) Send("{SPACE}") Sleep(16000) Send("{Space}") Sleep(10000) Send("COR") Send("{ENTER}") Sleep(5000) Send("{TAB}") Sleep(2000) EndFunc ;==>_RePrint Every time $iCnt gets to 50, it runs _RePrint() and resets $iCnt to 0. After the loop is done, if $iCnt indicates more were done since the last 50, then it runs _RePrint() one more time. Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now