sysadmin Posted March 30, 2011 Share Posted March 30, 2011 Ok... After a week of posting here, and going cross eyed reading help files I have almost finished writing my first script. What it is supposed to do... This company uses an aviation management software called CORRIDOR to run every aspect of out business. We have a lot of items in out inventory that have incorrect data. There are two ways to change this data. Do it one by one, or pay the company that owns the software to do it for us (hence the use of auto it). Now our items in inventory all have what is called a "lot number". This number is unique to each physical part. I have these lot numbers in an Excel spread sheet. What I need to know.... How do I get auto it to go down the list of lot numbers in excel and interact with our software? Link to comment Share on other sites More sharing options...
water Posted March 30, 2011 Share Posted March 30, 2011 (edited) To interact with Excel you can use the Excel UDF (User Defined Function) which is part of AutoIt (check the help file for User Defined Functions). How to interact with the application depends on what the application offers: If it has an import feature you can use this If you need to automate the GUI then first thingyou do is to check if you can access the GUI controls ... Edited March 30, 2011 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sysadmin Posted March 30, 2011 Author Share Posted March 30, 2011 Well, I already have the functionality with the GUI for the software worked out. All I have left to write is grabbing a number from excel, interacting with with my GUI for the software, and then grabbing the next number and doing its thing. Link to comment Share on other sites More sharing options...
water Posted March 30, 2011 Share Posted March 30, 2011 To extract data from Excel use _ExcelBookOpen und _ExcelReadCell. How to write this information into your GUI depends on the controls you use. GUICtrlSetData, Send .. will do what you need. We need more information to better help you. Do you have a screenshot of the Excel file where we can see which data you need to extract. And a screenshot of your Gui or of the AutoIt Window Info tool so we can see which kind of controls you use? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sysadmin Posted March 31, 2011 Author Share Posted March 31, 2011 I actually tried to upload some screenshots, but it said the file was too big to upload. Link to comment Share on other sites More sharing options...
sysadmin Posted March 31, 2011 Author Share Posted March 31, 2011 O.k. The highlighted column in Excel is that I need to pull from. I need the script to start at the first cell, copy the data that is there, interact with the my software's GUI, and then grab the next number from the list. Over the past week I have figured out how to get Auto it to work with my GUI. That part has gotten easy. Getting auto it to pull from Excel is my final step. I could also use a little help on something else. How do I get auto it to remove data from a field in my GUI and replace it with something new. I'm just not sure which command to use. Link to comment Share on other sites More sharing options...
water Posted March 31, 2011 Share Posted March 31, 2011 (edited) This litle script reads all rows from an Excel file and displays the value of column A.#include <excel.au3> $oExcel = _ExcelBookOpen("C:\temp\test.xls", 0) ; Open the Excel workbook Global $iLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row ; calculate the last used row For $iIndex = 1 To $iLastRow ; step through the rows $sValueA = _ExcelReadCell($oExcel, "A" & $iIndex) ; get value of cell Ax where x is the rownumber ConsoleWrite($sValueA & @CRLF) ; <== insert the code to write the value to the GUI Next ; end of loop _ExcelBookClose($oExcel) ; close Excel workbook) To insert the data into your Gui use GUICtrlSetData ( controlID, data [, default] ) Edited March 31, 2011 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sysadmin Posted March 31, 2011 Author Share Posted March 31, 2011 (edited) So how does auto it go down the list? It needs to run one lot number at a time. Edited March 31, 2011 by sysadmin Link to comment Share on other sites More sharing options...
water Posted March 31, 2011 Share Posted March 31, 2011 I've inserted comments in my previous post to explain how it works. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
sysadmin Posted March 31, 2011 Author Share Posted March 31, 2011 So here is my script. (its not 100% complete yet) Can you combine the two so I can see what I'm supposed to look like? Run("C:\CORRIDOR 9.50.23\Release\CORRIDOR.exe", "") WinWaitActive("System Login", "Production") WinActivate("System Login", "Database") ControlSend("System Login", "Password", 1002, "Password") Send("{ENTER}") WinWaitActive("CORRIDOR", "", 5) WinSetState("CORRIDOR", "", @SW_MAXIMIZE) Send("!t") Send("{down}") Send("{right}") Send("{down 3}") Send("{ENTER}") WinWaitActive("CORRIDOR", "", 5) ControlCommand("Current Parts in Inventory Search", "", "SysTabControl323", "TabRight", "") ControlClick("Current Parts in Inventory Search", "Less", 1947, "left") WinWait("Current Parts in Inventory Search", "", 3) ControlClick("Current Parts in Inventory Search", "Lot number", 1070, "left") ControlSend("Current Parts in Inventory Search", "Lot number", "Edit2", "LT11-02553") ControlClick("Current Parts in Inventory Search", "Scan", 1063, "left") ControlClick("Current Parts in Inventory Search", "Open", 1663, "left") Link to comment Share on other sites More sharing options...
sysadmin Posted March 31, 2011 Author Share Posted March 31, 2011 ok. here is my finished script and it didnt work.... expandcollapse popup#include <excel.au3> $oExcel = _ExcelBookOpen("C:\Users\mcrown\Documents\searchresultstest.xls", 0) Global $iLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row For $iIndex = 1 To $iLastRow $sValueA = _ExcelReadCell($oExcel, "A" & $iIndex) ConsoleWrite($sValueA & @CRLF) ; <== insert the code to write the value to the GUI WinWaitActive("CORRIDOR", "", 5) WinSetState("CORRIDOR", "", @SW_MAXIMIZE) Send("!t") Send("{down 2}") Send("{right}") Send("{down 3}") Send("{ENTER}") WinWaitActive("CORRIDOR", "", 5) ControlCommand("Current Parts in Inventory Search", "", "SysTabControl323", "TabRight", "") ControlClick("Current Parts in Inventory Search", "Less", 1947, "left") WinWait("Current Parts in Inventory Search", "", 3) ControlClick("Current Parts in Inventory Search", "Lot number", 1070, "left") ControlClick("Current Parts in Inventory Search", "Scan", 1063, "left") ControlClick("Current Parts in Inventory Search", "Open", 1663, "left") send("{TAB 7}") send("Lowcountry Trading IV, LLC") ControlCommand("Edit Lot", "", "SysTabControl321", "TabRight", "") ControlCommand("Edit Lot", "", "SysTabControl321", "TabRight", "") ControlCommand("Edit Lot", "", "SysTabControl321", "TabRight", "") ControlCommand("Edit Lot", "", "SysTabControl321", "TabRight", "") send("{TAB 4}") send("{ENTER}") send("{DOWN }") send("{ENTER}") Next _ExcelBookClose($oExcel) log of the runing script >"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\mcrown\Desktop\AutoIT\edit lot in test.au3" /autoit3dir "C:\Program Files\AutoIt3" /UserParams +>16:11:05 Starting AutoIt3Wrapper v.2.0.1.24 Environment(Language:0409 Keyboard:00000409 OS:WIN_7/ CPU:X64 OS:X86) >Running AU3Check (1.54.19.0) from:C:\Program Files\AutoIt3 +>16:11:06 AU3Check ended.rc:0 >Running:(3.3.6.1):C:\Program Files\AutoIt3\autoit3.exe "C:\Users\mcrown\Desktop\AutoIT\edit lot in test.au3" C:\Users\mcrown\Desktop\AutoIT\edit lot in test.au3 (3) : ==> Variable must be of type "Object".: Global $iLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row Global $iLastRow = $oExcel^ ERROR ->16:11:06 AutoIT3.exe ended.rc:1 >Exit code: 1 Time: 1.643 Link to comment Share on other sites More sharing options...
roadling Posted April 7, 2011 Share Posted April 7, 2011 Tell the smoking man this came all the way from Swindon expandcollapse popup#include <File.au3> #include <Array.au3> #include <Excel.au3> ; opens excel workbook ;REST THE WORKBOOK FOR YOUR FILE PATH AND NAME local $oExcel = _ExcelBookOpen("C:\autoit\lotnumbers.xls") ; reads data into array $aArray1 = _ExcelReadSheetToArray($oExcel,1,1,0,1,True) ; closes the excel workbook _ExcelBookClose($oExcel) ; displays the array that was read. _ArrayDisplay($aArray1) ; counts the number of rows in the array $rows = UBound($aArray1)-1 ; waits for Corridor to become active and maximizes the window WinActive("CORRIDOR", "") WinSetState("CORRIDOR","",@SW_MAXIMIZE) ; finds the window called Corridor and navigates to the parts in inventory WinActivate("CORRIDOR", "") send("!t") send("{down 2}") send("{right}") send("{down 3}") send("{ENTER}") ; waits for the Corridor Parts in Inventory to be active WinWaitActive("CORRIDOR", "", 5) WinActivate("Current Parts in Inventory Search", "") ;Navigates to Part tab and lot number field WinWaitActive("CORRIDOR", "", 5) sleep(500) ControlCommand("Current Parts in Inventory Search", "", "SysTabControl323", "TabRight", "") sleep(500) send("{TAB}") for $c = 1 to $rows WinWaitActive("Current Parts in Inventory Search", "Lot number:", 5) ;typo Lot Number should be Lot number: ControlClick("Current Parts in Inventory Search", "Lot number:", 1070, "left") ;Loads up lot number ControlSend("Current Parts in Inventory Search", "Lot number:", 1070, $aArray1[$c][0]) sleep(250) ControlClick("Current Parts in Inventory Search", "Lot number:", 1063, "left") ; Typo in window name, changed ID for scan button to 1063,changed text value sleep(250) ControlClick("Current Parts in Inventory Search", "Lot number:", 1663, "left") ; Changed text value ;need to wait for edit lot to become active WinWaitActive("Edit Lot", "Lot number:", 10) ;set new owner and vendor ;RESET these with Lowcountry Trading for your testing!!!!!!! ControlClick("Edit Lot", "Lot number:",1053) Send("{TAB}") ControlSend("Edit Lot", "Lot number:",1082,"{DEL}A/V ARKANSAS") Send("{TAB}") ControlSend("Edit Lot", "Lot number:",1142,"{DEL}A/V ARKANSAS") sleep(250) ;Click OK ControlClick("Edit Lot", "Lot number:", 1, "left") ;wait to get back to search dialog WinWaitActive("Current Parts in Inventory Search", "Lot number:", 5) Send("{ALT}s"&"n") next MsgBox(0,"Lot Updater",$rows&" lots have been updated.") 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