Pulling data from Excell

[sysadmin]

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?

[water]

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

[sysadmin]

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.

[water]

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?

[sysadmin]

I actually tried to upload some screenshots, but it said the file was too big to upload.

[sysadmin]

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.

[water]

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

[sysadmin]

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

[water]

I've inserted comments in my previous post to explain how it works.

[sysadmin]

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")

[sysadmin]

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

[roadling]

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.")