how to read data (text file or excel) and enter on web form

[ProfessorGreibowitz]

I don't plan to use this pgm much but wondered if someone could clue me in on how I could read a data, enter it into web form box, select one of two radio btns, click "save" and repeat until all rows are processed.

I have programmed before but not really sure about AutoIT or scripting. It's ok if I can export excel to a comma delimited file or if that'd be easier than reading from excel format. I only need to do this maybe once every two months but it's almost 600 entries. Wondering if I can save a few hours.

Can AutoIT read data and enter on a webpage with some way to sense the page is ready for the next entry? Sorry I'm so clueless! I didn't find an answer just searching the forum. Any help is appreciated.

Tim

[big_daddy]

This should be a fairly simple task.

• Take a look a the ExcelCOM UDF, this will allow you to pull the data from excel and store it into a variable for later use.
• Take a look at the _IE* functions in the help file, these will allow you to fill the form and submit.
• Come back here with any questions you may have.

[ProfessorGreibowitz]

thanks! that's a start. I am sure I'll be back with questions.

damn. back b4 i even post.

I see example scripts and a beta version of autoit but I don't see a download on that page for a file called excel udf or similar. Is this a file? Is this some text in a post somewhere that must be included in some other file via #include?

Sorry for the denseness. thanks again bd!

oh, i just found that excelcom.au3 is the file to include. That is in the examples.zip file. Ok. Making more sense.

Let me snoop some more. I have no idea how to write a .au3 script so this may take me a while.

tim

Edited September 12, 2006 by ProfessorGreibowitz

[ProfessorGreibowitz]

ok, so _XLRead(...) is how to read data and then something like var_name = _XLRead(...) and put that in a loop that will then enter the variable contents on the webpage?

let me read some more b4 I bug ya to death.

tim

[big_daddy]

Sounds like you are off to a good start.

[ProfessorGreibowitz]

i haven't spent much time on this today but I slopped some pseudo code down to just get me started. I added questions so if you can answer any quickly, it'd prob help.

#include <ExcelCom.au3>

#include <IE.au3>

$hotel_file = "hotel_list.xls"

$corporate_URL = "http://whatever.html"

make xl call to get # rows store in (num_hotels)

set row_number = 1

for (i=0; i<num_hotels; i++)

{

$hotel_code = _XLRead(...) reads data in current row (does this return a flag if last row is read? like eof?)

do I need to specify url in these ie actions?

_IEAction (enter $hotel_code into input box)

_IEAction (select "preferred" radio btn, use click action i assume)

_IEAction (ByRef $o_object <the submit btn>, $s_Click)

wait somehow till page is ready again for Input

bump row_number? or does XLread always know to do this? Do i need to use On Success??? if so, how? Use it to see if an entry bombed?

}

I'll keep reading the function specs and see if i can get a bit closer. Just haven't programmed 1 line of code in 2 years. Not a coding nerd.

tim

[randallc]

Hi,

ExcelCom was never intended to be very sophisticated;

Easiest to get the range you want into a 2D array in AutoIt, i think (_XLArrayRead), then deal with your loop from there and avoid too many com calls.

Best, randall [example in zip; "_XLArrayExample.au3"]

[ProfessorGreibowitz]

thanks, randallc! that actually simplifies it a bit. Tried to find out how to get the sizeof(array) but I guess I'm dumb! hehehe help didn't have size, length.. nuthin.

let me struggle some more.

tim

[randallc]

ubound($ar_Array)-1

Randall

[ProfessorGreibowitz]

ok, thanks a lot! this forum is a big help to an "almost" programmer like me.

[ProfessorGreibowitz]

okay, having problems just opening the page i need. I have to log into a page, then nAVIGATE TO THE RIGHT URL IN THAT SAME WINDOW OR dang it... sorry, or it won't work due to some session id coding.

I can't see how to set $oIE to the current window w/o doing a _IECreate.

help, please.

thanks,

tim

[ProfessorGreibowitz]

ok, got it. attach then navigate.

answering myself... what does that say?

tim

[ProfessorGreibowitz]

ok. Now, getting errors at runtime. here's my script. corp URL is removed but it is working now. errors on last 3 lines.

#include <ExcelCom.au3>

#include <IE.au3>

; TO ADD PREFFERED HOTELS FOR A CORPORATE ACCT, JUST CHANGE THE FOLLOWING TWO LINES

; THE URL AND CELL RANGE ARE ALL YOU NEED TO CHANGE AND BE SURE TO

; PUT YOUR EXCEL FILE IN YOUR SCRIPT DIRECTORY AND NAME IT HOTEL_LIST.XLS

;

$corporate_URL = "https://wherever"

$cell_range = "A1:A560"

$FilePath=@ScriptDir & "\hotel_list.xls"

$hotel_array =_XLArrayRead($FilePath,1,$cell_range) ; 1 means sheet 1

$nbr_hotels = UBound($hotel_array)-1 ; stores # of hotels

$oIE = _IEAttach ("eAdvantage help desk", "text")

_IENavigate ($oIE, $corporate_URL)

_IELoadWait ($oIE) ; wait til page loads completely

$oForm = _IEFormGetObjByName ($oIE, "frmAddHotels") ; create form object ref

$oText = _IEFormElementGetObjByName ($oForm, "txtHotelID") ; create obj to ref hotel box

_IEFormElementSetValue ($oText, "yee haw!") ; enter one hotel into input box

--> IE.au3 Error from function _IEPropertyGet, $_IEStatus_InvalidObjectType

--> IE.au3 Warning from function _IEFormGetObjByName, $_IEStatus_NoMatch

--> IE.au3 Error from function _IEFormElementGetObjByName, $_IEStatus_InvalidDataType

--> IE.au3 Error from function _IEFormElementSetValue, $_IEStatus_InvalidDataType

Don't I just use form names in html? help please!

tim

[big_daddy]

This sounds like it could be a frames issue. If you could provide the source of the webpage it would make it easier to troubleshoot.

[ProfessorGreibowitz]

i found out the prob. man.... someone had commented the 2nd form tag! So, after 45 min of scratching my bald head.... I realized it was commented. I first thought it was a script comment to hide from older browsers.

once i renamed to FIRST form name... bingo.

thanks for checking in on me! seems I figure it out right after I post. grrrrrrr

tim

[ProfessorGreibowitz]

I got it working! hahahahaha man, I fed 559 hotel codes into a form one at a time. took maybe 10 minutes.

Thanks a lot for the help. This is a pretty cool pgm.

tim