Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

Pulling data from Excell


  • Please log in to reply
11 replies to this topic

#1 sysadmin

sysadmin

    Seeker

  • Active Members
  • 17 posts

Posted 30 March 2011 - 01:17 PM

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?







#2 water

water

    ?

  • MVPs
  • 15,566 posts

Posted 30 March 2011 - 01:27 PM

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 by water, 30 March 2011 - 01:27 PM.

UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#3 sysadmin

sysadmin

    Seeker

  • Active Members
  • 17 posts

Posted 30 March 2011 - 07:32 PM

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.

#4 water

water

    ?

  • MVPs
  • 15,566 posts

Posted 30 March 2011 - 10:27 PM

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?
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#5 sysadmin

sysadmin

    Seeker

  • Active Members
  • 17 posts

Posted 31 March 2011 - 02:25 PM

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

#6 sysadmin

sysadmin

    Seeker

  • Active Members
  • 17 posts

Posted 31 March 2011 - 02:37 PM

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.

Attached Thumbnails

  • excell screen.jpg
  • corridor screen shot.png


#7 water

water

    ?

  • MVPs
  • 15,566 posts

Posted 31 March 2011 - 02:50 PM

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 by water, 31 March 2011 - 03:13 PM.

UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#8 sysadmin

sysadmin

    Seeker

  • Active Members
  • 17 posts

Posted 31 March 2011 - 03:03 PM

So how does auto it go down the list? It needs to run one lot number at a time.

Edited by sysadmin, 31 March 2011 - 03:04 PM.


#9 water

water

    ?

  • MVPs
  • 15,566 posts

Posted 31 March 2011 - 03:14 PM

I've inserted comments in my previous post to explain how it works.
UDFs:
Active Directory (NEW 2014-07-21 - Version 1.4.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2014-07-27 - Version 1.0.0.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2013-01-21 - Version 0.3.1.1) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
Tutorials:
ADO - Wiki

#10 sysadmin

sysadmin

    Seeker

  • Active Members
  • 17 posts

Posted 31 March 2011 - 03:48 PM

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?

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


#11 sysadmin

sysadmin

    Seeker

  • Active Members
  • 17 posts

Posted 31 March 2011 - 08:16 PM

ok. here is my finished script and it didnt work....

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

#12 roadling

roadling

    Seeker

  • Normal Members
  • 2 posts

Posted 07 April 2011 - 10:24 PM

Tell the smoking man this came all the way from Swindon

Plain Text         
#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.")





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users