Help copying excel to a web page form

[brucejohn22]

I need some help getting started with making a script to complete a specific task.

TASK:

Copy information from EXCEL to a web page and submit the information.

The excel document has two columns one column contains the email address and the other column contains a customer number.

I also am lost on how to make this loop

Can some one point me in the right direction to some example code. I have been looking for the last 2 days for some good example code but I have not found anything that seems to work for my situation. Any help will be appreciated.

Thank you

[brucejohn22]

this is what I have so far is there a better way to do this?

WinActivate("Microsoft Excel - Book1")

For $i = 1 To 400

WinActivate("Microsoft Excel - Book1")

WinWaitActive("Microsoft Excel - Book1")

Send("^c")

Sleep(100)

Send("{DOWN}")

WinActivate("webpage")

WinWaitActive("webpage")

MouseClick("left",200,-263,0)

Send("^v")

Sleep(100)

Send("on{ENTER}")

Next

[PsaltyDS]

this is what I have so far is there a better way to do this?

WinActivate("Microsoft Excel - Book1")

For $i = 1 To 400

WinActivate("Microsoft Excel - Book1")

WinWaitActive("Microsoft Excel - Book1")

Send("^c")

Sleep(100)

Send("{DOWN}")

WinActivate("webpage")

WinWaitActive("webpage")

MouseClick("left",200,-263,0)

Send("^v")

Sleep(100)

Send("on{ENTER}")

Next

Talk about doing it the HARD way!

Try this:

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

$sExcelFile = "C:\Temp\ExcelFile.xls"
$oExcel = _ExcelBookOpen($sExcelFile)
$avExcel = _ExcelReadSheetToArray($oExcel)

For $r = 1 To $avExcel[0][0]
    $sEmail = $avExcel[$r][1]; assumes email in column 1 (A)
    $sCust = $avExcel[$r][2]; assumes customer number in column 2 (B)
    
; Replace the following with appropriate _IE* functions
    MsgBox(64, "Row " & $r, "Email = " & $sEmail & @CRLF & "Customer number = " & $sCust)
Next

Edit $sExcelFile with the correct path, and the column numbers for email and customer number if they are wrong (I assumed 1 and 2).

The MsgBox() is a place holder for the correct _IE* functions to place the data as required, for example see _IEFormElementSetValue() in the help file.

[ssubirias3]

@brucejohn22 - Welcome to autoit and the forum! the best thing about this place are folks like psaltyds who are willing to share their knowledge with you. As long as you make an effort and pay attention. Remember there's always more than one way to skin a cat. Speaking of which, to use psaltyds' code you'll need to download a couple of udf (user defined functions).

@psaltyds - hey brother, long time no speak! I'm still knocking off some of this rust from not being active in a while. Which of the following two Excel UDF's do you suggest/prefer? Thanks in advance!

locodarwin's version: http://www.autoitscript.com/forum/index.php?showtopic=34302

randallc's version: http://www.autoitscript.com/forum/index.php?showtopic=14166

EDIT: disregard question, after further review its clear randallc recommends locodarwin's version and psaltyds contributed to locodarwin's version.

Edited September 12, 2008 by ssubirias3

[Community On Patrol]

Hi brucejohn22,

1st Welcome to the AutoIt Forums!

Some of the following tips may not apply to you, but it may make your life a bit easier here on the forum in the future.

CODE

• Did you know that we have an awesome search feature?

  You can find many answers to your current questions, just by typing in the right search patterns.

• A suggestion is to use the Advanced Search mode:

• Type your specific search term in quotes.
• Click the forum you want to search in (the one most likely to have your information would generally be the Example Script forum and or the General Help and Support Forum).
• Click on "Search titles only" radio button.
• Click perform search.

The above will help you narrow down your searches and prevent you from unneccesarily posting a new thread.

[*]Also, you should try to read the Sticky posts that are at the top of each of the AutoIt Forums you enter such as:

• FAQs (Frequently Asked Questions)

• Are my AutoIt EXEs really infected? (About false positives and what to do if you may encounter one)

[*]Forum Etiquette:

• Making a new thread:
  • Use the Search feature first to see if your question has already been answered.
  • Look in the help file as well before even thinking of posting (When what you want could be obtained by simply reading the help file, you don't generally get a good response from your AutoIt community).
  • Titles are very important here. 1 word titles or titles like "help me", "write something for me", "I'm a noob" etc... aren't tolerated.
  • Use common sense when creating a new thread.

    Ask yourself if the title is descriptive enough to even interest someone (preferably those that know what they are talking about) to even look at your thread, let alone reply in it.

  • Think about how it would show in the search feature if someone were to look for something just like you are looking for (think of the keywords you used yourself and obviously didn't find anything (because we know you used the search feature ) and use those types of keywords in your title as well).
• Thread content:
  • Be descriptive with your query. (Make sure we actually know what you want to do).
  • Show you've made an effort in coding what you want (provide the reproducer code (generally no more than 50 lines as people lose interest in debugging someones script for free)).
  • Don't talk in ebonics. A lot of the forum members are adults, and a lot of them know how to help you, but talk like a child, you'll be treated as such.
  • Don't ask for help making keyloggers, spam (even if it's to do as a prank), or anything that can be thought of as malicious. You'll more than likely have the thread locked by a moderator, and take a bashing from your fellow AutoIt community.
  • When posting code, use code boxes. This can be accomplished by using [code ]<content here>[/code ] (No spaces between the brackets []).

    Using code boxes will keep the indentation and make it easier to read for others to help you.

• Bumping your threads:
  • Use common courtesy here.

    Keep in mind every time you bump your thread to the top of the forum, you knock the other threads down a notch.

    Everyone posting for help has just as much right for their threads to get read as you do.

    Because of that, do not bump your post more than once in a 24 hour period.

    A Bump is simply posting in your thread with nothing that pertains to your query with the sole purpose of moving it up.

    Deleting previous bumps, and posting new ones is not tolerated, and the moderators can find those deletions, so do yourself a favor and don't cross that line >_< .

• Rude or obnoxious content:

  This falls pretty much under the common sense thing. If you use it (common sense) before posting, you won't have issues.

  • Don't use foul language, remember, a lot of the community is at work when they read these threads.
  • Don't provoke or instigate an argument with someone.
• Double Posting:
  • It's understood that sometimes there's a lag in the system, and sometimes people don't see their post go up right aways so they post again.

    If this happens to you, simply notify a moderator with the report feature in the post, and politely ask them to delete it.

  • If you're just creating another topic because your original topic is not being answered the way you want or at all, this is not tolerated. You could lose your posting privileges all together over it.
• Non-English languages
  • If English is not your primary language, please make an attempt to interpret (yourself or online) and post that interpretation.

    We have wonderful users from around the world, so after you've done your post in English, back it up with your question also in your native tongue (You may find your answer much quicker using both).

That's it for now, I hope you have a wonderful learning experience, and hope to see you contribute to the community as your knowledge grows.

[PsaltyDS]

@brucejohn22 - Welcome to autoit and the forum! the best thing about this place are folks like psaltyds who are willing to share their knowledge with you. As long as you make an effort and pay attention. Remember there's always more than one way to skin a cat. Speaking of which, to use psaltyds' code you'll need to download a couple of udf (user defined functions).

@psaltyds - hey brother, long time no speak! I'm still knocking off some of this rust from not being active in a while. Which of the following two Excel UDF's do you suggest/prefer? Thanks in advance!

locodarwin's version: http://www.autoitscript.com/forum/index.php?showtopic=34302

randallc's version: http://www.autoitscript.com/forum/index.php?showtopic=14166

EDIT: disregard question, after further review its clear randallc recommends locodarwin's version and psaltyds contributed to locodarwin's version.

You are farther out of date than you know. Many of the ExcelCOM_UDF functions from Locodarwin and others are now Excel.au3 and included with AutoIt since the 3.2.13.4 Beta. That's why you see my demo using Excel.au3 vice ExcelCOM_UDF.au3.

[ssubirias3]

You are . . . out of date . . . Excel.au3 . . . included with AutoIt since the 3.2.13.4 Beta. That's why you see my demo using Excel.au3 vice ExcelCOM_UDF.au3.

LOL... ok, ok, as usual you're right once again; almost kinda sorta!! I am using the current production version (3.2.12.1 released 6/12/08) of AutoIt.

The other thing that kinda threw me off was when I downloaded locodarwin's zip, the UDF inside was titled Excel.au3 not ExcelCom_UDF.au3. And because the production Include UDF's are *SO* out of date, when I copied locodarwin's UDF over to my Include folder . . . I wasn't asked about replacing the current copy.

Thanks again psalty or the schooling, you Venerable Old Bird you!

[brucejohn22]

Thank you for your help. I think this has pointed me in the right direction.

I do have another question the web page that I am pasting data to does not have a FORM name?

looking at the example code provided it looks like f = the form name. Is that right?

#include <IE.au3>

$oIE = _IECreate ("http://www.google.com")

$oForm = _IEFormGetObjByName ($oIE, "f")

$oQuery = _IEFormElementGetObjByName ($oForm, "q")

_IEFormElementSetValue ($oQuery, "AutoIt IE.au3")

_IEFormSubmit ($oForm)

I am using Debugbar to look at the form on the web site and this is what it looks like:

<FORM action=edit_user.cgi method=post>Email address to add or edit: <INPUT size=40 name=email>

<P><INPUT type=submit value=Edit> </FORM>

Since it has no name how can I approach this?

[PsaltyDS]

Thank you for your help. I think this has pointed me in the right direction.

I do have another question the web page that I am pasting data to does not have a FORM name?

looking at the example code provided it looks like f = the form name. Is that right?

#include <IE.au3>

$oIE = _IECreate ("http://www.google.com")

$oForm = _IEFormGetObjByName ($oIE, "f")

$oQuery = _IEFormElementGetObjByName ($oForm, "q")

_IEFormElementSetValue ($oQuery, "AutoIt IE.au3")

_IEFormSubmit ($oForm)

I am using Debugbar to look at the form on the web site and this is what it looks like:

<FORM action=edit_user.cgi method=post>Email address to add or edit: <INPUT size=40 name=email>

<P><INPUT type=submit value=Edit> </FORM>

Since it has no name how can I approach this?

A FORM doesn't necessarily have a name. Using _IEFormObjGetCollection() you can get a collection of all forms and sort through them in a loop, or get a specific one by 0-based index. For example $oForm = _IEFormObjGetCollection($oIE, 0) gets the first form, and 1 would be the second form, etc.

[brucejohn22]

A FORM doesn't necessarily have a name. Using _IEFormObjGetCollection() you can get a collection of all forms and sort through them in a loop, or get a specific one by 0-based index. For example $oForm = _IEFormObjGetCollection($oIE, 0) gets the first form, and 1 would be the second form, etc.

PsaltyDS thank you for all of your help I think this is my last question for this post. If any one can help please do so.

I think i have everything working but I realized last night that I need to clear/delete the customer ID field before pasting the information what is the most efficient way of doing this? Please point me in the right direction.

[PsaltyDS]

PsaltyDS thank you for all of your help I think this is my last question for this post. If any one can help please do so.

I think i have everything working but I realized last night that I need to clear/delete the customer ID field before pasting the information what is the most efficient way of doing this? Please point me in the right direction.

Get an object reference to that form element and then use _IEFormElementSetValue().