Sign in to follow this  
Followers 0
ProfessorGreibowitz

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

16 posts in this topic

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

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by ProfessorGreibowitz

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Sounds like you are off to a good start.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

ok, got it. attach then navigate.

answering myself... what does that say?

tim

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0