Array help!

[padinski]

Hi all, long time listener, first time caller.

Some background:
I'm trying to update our addressing database at work. Each address has a unique "id", against each address there are 3 fields that need to get updated.
The information is presented in an excel spreadsheet. Column A is the address id, columns B,C & D contain the 3 different fields that need to be updated.

My script works fine for the amount of addresses we currently update, it takes about 13 seconds to do each update.
However, we are looking at increasing our volumes of address updates, & I was hoping someone had a better way of doing this.

If you are wondering why I'm using the function winactivate, it's just so I can occasionally look over at the pc the script is running on, to see where the bot is up to in the spreadsheet & if the addresses are being updated correctly.

2 forms need to be submitted. The first with the address id, once that page loads, the 2nd form is submitted with the other attributes populated. The script then continues a loop depending on how many records I have to update.

(Form elements & links have been changed)

expandcollapse popup

;Script Start - Add your code below here

#include <IE.au3>

Opt("WinTitleMatchMode", 2)

Local $a = 0;Loop counter variable
Do
    $a +=1;Adds 1 to the loop counter

;-----------------------------------------------------------------------------
;Copy address id from excel - select the cell above the first id you want to start at
;-----------------------------------------------------------------------------
Sleep(500)
WinActivate("Excel")
WinWaitActive("Excel")
Sleep(500)
Send("{HOME}")
Sleep(500)
Send("{DOWN}")
Sleep(500)
Send("^c")
Sleep(500)
WinActivate("Address")
WinWaitActive("Address")
;-----------------------------------------------------------------------------
;Address id search in Address
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
$oForm1 = _IEFormGetCollection($oIE, 0)
Local $oQuery = _IEFormElementGetObjByName($oForm1, "AddressID")
_IEFormElementSetValue($oQuery, ClipGet())
_IEFormSubmit($oForm1)
_IELoadWait($oIE)
;-----------------------------------------------------------------------------
;Copy cell b from excel
;-----------------------------------------------------------------------------
WinActivate("Excel")
WinWaitActive("Excel")
Sleep(500)
Send("{RIGHT}")
Sleep(500)
Send("^c")
Sleep(500)
WinActivate("Address")
WinWaitActive("Address")
;-----------------------------------------------------------------------------
;Update field a in Address
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
$oForm2 = _IEFormGetCollection($oIE, 0)
Local $oQuery1 = _IEFormElementGetObjByName($oForm2, "Field A")
_IEFormElementSetValue($oQuery1, ClipGet())
;-----------------------------------------------------------------------------
;Copy cell c from excel
;-----------------------------------------------------------------------------
WinActivate("Excel")
WinWaitActive("Excel")
Sleep(500)
Send("{RIGHT}")
Sleep(500)
Send("^c")
Sleep(500)
WinActivate("Address")
WinWaitActive("Address")
;-----------------------------------------------------------------------------
;Update field b in Address
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
Local $oQuery2 = _IEFormElementGetObjByName($oForm2, "Field B")
_IEFormElementSetValue($oQuery2, ClipGet())
;-----------------------------------------------------------------------------
;Copy cell d from excel
;-----------------------------------------------------------------------------
WinActivate("Excel")
WinWaitActive("Excel")
Sleep(500)
Send("{RIGHT}")
Sleep(500)
Send("^c")
Sleep(500)
WinActivate("Address")
WinWaitActive("Address")
;-----------------------------------------------------------------------------
;Update field c in Address
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
Local $oQuery3 = _IEFormElementGetObjByName($oForm2, "Field C")
_IEFormElementSetValue($oQuery3, ClipGet())
;-----------------------------------------------------------------------------
;Update Date in Address
;-----------------------------------------------------------------------------
Local $oQuery4 = _IEFormElementGetObjByName($oForm2, "Date")
_IEFormElementSetValue($oQuery4, "01-06-2015");Set to the first of the month
;-----------------------------------------------------------------------------
;Submit form & prepare for next address
;-----------------------------------------------------------------------------
_IEFormSubmit($oForm2)
_IELoadWait($oIE)
WinWaitActive("Message")
ControlClick("Message", "OK", "[CLASS:Button;INSTANCE:1]");Confirmation message box, click "ok"
Sleep(500)
_IENavigate($oIE, "http://intranet/addresssearch.jsp")
;-----------------------------------------------------------------------------
;How many times to loop
;-----------------------------------------------------------------------------
Until $a = 150;Set to number of rows in excel-1

I believe an array is the best way to go about increasing the speed, but I can't for the life of me figure it out. I've read the help files & searched for examples, but I can't find anything that fits this scenario of needing to submit 2 forms, the 2nd with multiple fields then to continue looping until no more records exist to update.

Can anyone help me on this? Thanks in advance. 

[JohnOne]

The short answer which is all I have time for is to use the standard Excel UDF's which can be found in the help file, to get shut of all the Send and Win* business.

long time listener, first time caller

lol.

[Jfish]

100% agree with @JohnOne.  The Excel UDF would be much faster and cleaner than doing it by sending keystrokes.  Check the help file under user defined functions.

[padinski]

cheers guys I'll do some more reading on the excel UDF & post back with an update, appreciate the suggestion.

[padinski]

Thanks for your advice, looks much cleaner. The issue is I can get it to copy the 1st row of cells, but now with my loop how do I get it to start copying from the next row?

Example of what I have now: Obviously it's not going to work with my cells being A1,B1,C1,D1

expandcollapse popup

;-----------------------------------------------------------------------------
;Copy address id from excel - 
;-----------------------------------------------------------------------------
Local $oRange1 = $oWorkbook.ActiveSheet.Range("A1")
_Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange1)
;-----------------------------------------------------------------------------
;Address id search in IE
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
$oForm1 = _IEFormGetCollection($oIE, 0)
Local $oQuery = _IEFormElementGetObjByName($oForm1, "AddressID")
_IEFormElementSetValue($oQuery, ClipGet())
_IEFormSubmit($oForm1)
_IELoadWait($oIE)
;-----------------------------------------------------------------------------
;Copy cell b from excel
;-----------------------------------------------------------------------------
Local $oRange2 = $oWorkbook.ActiveSheet.Range("B1")
_Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange2)
;-----------------------------------------------------------------------------
;Update field a in Address
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
$oForm2 = _IEFormGetCollection($oIE, 0)
Local $oQuery1 = _IEFormElementGetObjByName($oForm2, "Field A")
_IEFormElementSetValue($oQuery1, ClipGet())
;-----------------------------------------------------------------------------
;Copy cell c from excel
;-----------------------------------------------------------------------------
Local $oRange3 = $oWorkbook.ActiveSheet.Range("C1")
_Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange3)
;-----------------------------------------------------------------------------
;Update field b in Address
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
Local $oQuery2 = _IEFormElementGetObjByName($oForm2, "Field B")
_IEFormElementSetValue($oQuery2, ClipGet())

 

I was trying something along these lines, which doesn't work do you guys have any pointers or help me with the coding?
Basically I'm trying to get it to continue looping until Column A has an empty cell, but I'm not getting anywhere
 

expandcollapse popup

; Script Start - Add your code below here

#include <IE.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>


$sFilePath = "C:\Users\XXXX\Documents\autoittest.xlsx"

; *****************************************************************************
; Attach to workbook
; *****************************************************************************

Local $oWorkbook = _Excel_BookAttach($sFilePath)
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF:", "Error attaching workbook '" & @ScriptDir & "unknown file." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf



;-----------------------------------------------------------------------------
;Copy address id from excel - 
;-----------------------------------------------------------------------------
For $i=1 to 4
Local $oRange1 = $oWorkbook.ActiveSheet.Range ($i,1)
If $oRange1="" Then ExitLoop 
_Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange1)
;-----------------------------------------------------------------------------
;Address id search in IE
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
$oForm1 = _IEFormGetCollection($oIE, 0)
Local $oQuery = _IEFormElementGetObjByName($oForm1, "AddressID")
_IEFormElementSetValue($oQuery, ClipGet())
_IEFormSubmit($oForm1)
_IELoadWait($oIE)
;-----------------------------------------------------------------------------
;Copy cell b from excel
;-----------------------------------------------------------------------------
Local $oRange2 = $oWorkbook.ActiveSheet.Range ($i,2)
_Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange2)
;-----------------------------------------------------------------------------
;Update field a in Address
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
$oForm2 = _IEFormGetCollection($oIE, 0)
Local $oQuery1 = _IEFormElementGetObjByName($oForm2, "Field A")
_IEFormElementSetValue($oQuery1, ClipGet())
;-----------------------------------------------------------------------------
;Copy cell c from excel
;-----------------------------------------------------------------------------
Local $oRange3 = $oWorkbook.ActiveSheet.Range ($i,3)
_Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange3)
;-----------------------------------------------------------------------------
;Update field b in Address
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
Local $oQuery2 = _IEFormElementGetObjByName($oForm2, "Field B")
_IEFormElementSetValue($oQuery2, ClipGet())
;-----------------------------------------------------------------------------
;Copy cell d from excel
;-----------------------------------------------------------------------------
Local $oRange4 = $oWorkbook.ActiveSheet.Range ($i,4)
_Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange4)
;-----------------------------------------------------------------------------
;Update field c in Address
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
Local $oQuery3 = _IEFormElementGetObjByName($oForm2, "Field C")
_IEFormElementSetValue($oQuery3, ClipGet())
;-----------------------------------------------------------------------------
;Update Date in Address
;-----------------------------------------------------------------------------
Local $oQuery4 = _IEFormElementGetObjByName($oForm2, "Date")
_IEFormElementSetValue($oQuery4, "01-06-2015");Set to the first of the month
;-----------------------------------------------------------------------------
;Submit form & prepare for next address
;-----------------------------------------------------------------------------
_IEFormSubmit($oForm2)
_IELoadWait($oIE)
_IENavigate($oIE, "http://intranet/addresssearch.jsp")
Next

 

Edited July 13, 2015 by padinski
typo in coding

[padinski]

Or should i be doing a 2d array using _excel_RangeRead. I can an array table to display fine with no troubles.

I guess I need a loop that runs through all rows and then an inner loop that runs through all columns. With every new row the inner loop starts with the first column of this row again? Not sure how i achieve this..

Also I haven't really figured out how to get each cell of data from the array into my ie functions, any guidance or advice would be greatly appreciated.

 

 

Edited July 13, 2015 by padinski
further info

[padinski]

Fixed this with the help of user Bremen. Absolute legend!

He's my script if anyone needs to use it in the future:

expandcollapse popup

;Script Start - Add your code below here

#include <Array.au3>
#include <IE.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Debug.au3>

Local $sFilepath="D:\Users\xxxx\Documents\test.xlsx"
;-----------------------------------------------------------------------------
; Create application object or connect to an already running Excel instance
;-----------------------------------------------------------------------------
Local $oAppl = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF:", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;-----------------------------------------------------------------------------
;open workbook from excel
;-----------------------------------------------------------------------------
$oWorkbook = _Excel_BookOpen($oAppl, $sFilepath, Default, Default, True)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF:", "Error opening '" & $oWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
;-----------------------------------------------------------------------------
;Read array from excel
;-----------------------------------------------------------------------------
Local $aArray1 = _Excel_RangeRead($oWorkbook, Default)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF:", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_ArrayDisplay($aArray1)
;-----------------------------------------------------------------------------
; counts the number of rows in the array
;-----------------------------------------------------------------------------
$rows = UBound($aArray1) -1
;-----------------------------------------------------------------------------
; script start
;-----------------------------------------------------------------------------
for $c = 1 to $rows
;-----------------------------------------------------------------------------
;Address id search in IE
;-----------------------------------------------------------------------------
$oIE = _IEAttach("Address")
$oForm1 = _IEFormGetCollection($oIE, 0)
Local $oQuery = _IEFormElementGetObjByName($oForm1, "AddressID")
_IEFormElementSetValue($oQuery, $aArray1[$c][0])
_IEFormSubmit($oForm1)
_IELoadWait($oIE)
;-----------------------------------------------------------------------------
;Update field1 in IE
;-----------------------------------------------------------------------------
$oForm2 = _IEFormGetCollection($oIE, 0)
Local $oQuery1 = _IEFormElementGetObjByName($oForm2, "field1")
_IEFormElementSetValue($oQuery1, $aArray1[$c][1])
;-----------------------------------------------------------------------------
;Update field2 in IE
;-----------------------------------------------------------------------------
Local $oQuery2 = _IEFormElementGetObjByName($oForm2, "field2")
_IEFormElementSetValue($oQuery2, $aArray1[$c][2])
;-----------------------------------------------------------------------------
;Update field3 in IE
;-----------------------------------------------------------------------------
Local $oQuery3 = _IEFormElementGetObjByName($oForm2, "field3")
_IEFormElementSetValue($oQuery3, $aArray1[$c][3])
;-----------------------------------------------------------------------------
;Update date in IE
;-----------------------------------------------------------------------------
Local $oQuery4 = _IEFormElementGetObjByName($oForm2, "date")
_IEFormElementSetValue($oQuery4, "01-06-2015");Set to the first of the month
;-----------------------------------------------------------------------------
;Submit form & prepare for next address
;-----------------------------------------------------------------------------
_IEFormSubmit($oForm2)
_IELoadWait($oIE)
WinWaitActive("Message")
ControlClick("Message", "OK", "[CLASS:Button;INSTANCE:1]");Confirmation message box, click "ok"
Sleep(500)
_IENavigate($oIE, "http://intranet/addaddresssearch")
next

Thanks!

Edited July 14, 2015 by padinski

[padinski]

end topic.

Edited July 26, 2015 by padinski