Wednesday, February 24, 2010

Excel and IE automation - the WSH way

I can think of two possible scenarios when automating Excel and Internet Explorer makes any sense:
  • you have data stored in Excel sheets and you want to push data into online web forms.

  • you need to extract data from web and save them into Excel xls file.
There are two ways to implement this:
  • automate Internet Explorer browser from Excel VBA macro.

  • automate both IE and Excel from an external process using OLE automation.
Today I will talk about the second option; I will automate both Internet Explorer and Excel application from a WSH script using Twebst Automation Studio. Here's a diagram showing the work-flow:


Fill out web forms with Excel data

The WSH web macro in this tutorial opens an XLS file containing phrases to be translated using Google Translate tool. It then automatically gets each phrase, opens an IE browser, navigates to translation page, fills out the text to be translated, press submit button, gets back the result and saves it into the same XLS file.

That's all for now! (as I try to keep articles relatively short) Meanwhile, you should download Twebst Automation Studio and the web macro sample to see it at work. Then, you can continue with the next article explaining the web macro code and comments: Extract web data into Excel.Application with browser macro

Downloads:

ExcelToWebMacro.zip

Twebst Automation Studio (FREE edition available btw)

5 comments:

sourabh bora said...

This is awesome. I was using the iMacros free edition so far, but this one is far easier. Thanks

CodeCentrix said...

If you like Twebst product and want to help it, just spread the word about it in your professional community.

Thank you for your kind words!

eman said...
This comment has been removed by the author.
eman said...

I like the idea. I'm trying to automate something similar from excel to web forms. THe web form has Next, Next before SUBMIT. I'd like to lift cell by cell, row by row, column by column sequentially into the web form. Please, HOW can I do that??

Adrian Dorache said...

You can start with the web automation part; Twebst web recorder can help here.

For the Excel part (and other automation details) you'll have to get your hands dirty and write some code.