The Joel on Software Discussion Group (CLOSED)

A place to discuss Joel on Software. Now closed.

This community works best when people use their real names. Please register for a free account.

Other Groups:
Joel on Software
Business of Software
Design of Software (CLOSED)
.NET Questions (CLOSED)
TechInterview.org
CityDesk
FogBugz
Fog Creek Copilot


The Old Forum


Your hosts:
Albert D. Kallal
Li-Fan Chen
Stephen Jones

How to transfer data from MS-Excel to a Website with VBScript?

I am a Sysadmin, hence such an elementary question for you programmer folk.

I have a list of users on a spreadsheet. I need to logon to a website, enter a user's name, be presented with a web-form for that user, fill-in some details that are there on the spreadsheet, click on a couple of buttons and move on to the next user in the list. The only tool I have at my disposal to automate my task is VBScript.

Grateful for any tips on how to proceed.
Sudden
Thursday, January 25, 2007
 
 
*shudder*

I don't even want to think about it.
anon for this one
Thursday, January 25, 2007
 
 
Sendkeys.

Unfortunately you'd spend more time scripting and debugging than it would take to get an intern or two (or somebody's kid) to handle it.
Oy!
Thursday, January 25, 2007
 
 
Use Perl.
I Have Issues Send private email
Thursday, January 25, 2007
 
 
> Use Perl.

I thought that Sudden was masochist... :P
Masiosare Send private email
Thursday, January 25, 2007
 
 
If you are lucky you can submit the data in the URL. Is so you can use excel's (assuming you are using excel) HYPERLINK function to build the URL, and then either just click all the links, or use an excel macro to browse to the URL's

You could also use Excel VBA to host an instance of Internet Explorer, and then also use VBA to fill out the form in IE  and submit it for you, and go to the next, but that would require some programming in VBA
ThMoJe
Thursday, January 25, 2007
 
 
MSDN's Scripting Guy offers some clue.  Try http://msdn2.microsoft.com/en-us/library/ms974568.aspx
AntHoney Send private email
Thursday, January 25, 2007
 
 
http://www.openqa.org/selenium-ide/

You can use selenium ide to record clicks via firefox. Then save the file as html. Edit the html copy and paste the steps x times and fill in the blanks for the form fields. This woule not take long and the learning curve is  not steep. You could script the copy paste and replace with vbs. Save the html and 'play' in selenium ide and watch the action.
irgop Send private email
Thursday, January 25, 2007
 
 
You can create a 'connection' to the Excel file with ADO:
http://support.microsoft.com/kb/257819#

Then you can use SQL to get the data:
http://support.microsoft.com/kb/195951

Then create a HTTP connection object to post all the data to the website:
http://www.motobit.com/tips/detpg_post-binary-data-url/
Mark Send private email
Thursday, January 25, 2007
 
 
Or use ADO to get at the spreadsheet data in an HTA.  This HTA could contain an IFRAME that it automate to navigate the web application.  By manipulating the IFRAME's DOM the parent HTA would be able to handle the form-filling and submissions.

All of the code could be VBScript, or if you're a contrarian you could use JScript as well.

The tricky part is setting up the proper cross-frame and cross-domain scripting access but it shouldn't be hard to give the parent HTA access to the IFRAME DOM without allowing the external pages loaded into the IFRAME any access back into the parent document.

The IFRAME can be run visible for development and testing, then invisible for regular use.

This kind of thing is why we have HTAs.  Much less clunky than automating an IE instance from a WSH script.


Of course I agree this is a lot of effort for a one-shot need.  Even someone who does this every day might want 8 hours to burn to get it debugged and documented.  Maybe just 4 hours if they have an example lying around to rework.  Those are still tight estimates that turn a blind eye to Murphy.

Your problem will be the Excel input: people tend to produce some trashy spreadsheets no matter how much you lecture them on the need for regularity.  You'll want your script to do an edit pass over all the data before it tries to actually post anything.
Codger
Thursday, January 25, 2007
 
 
+1 for Selenium.
GUI Junkie Send private email
Friday, January 26, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz