Thursday, February 25, 2010

Extract web data into Excel.Application with browser macro

Let's continue my previous post where I presented a browser macro that automatically translates phrases in an Excel document using Google Translate tool. As you cand see in code, the WSH script performs the following steps:
  • instantiate "Twebst.Core" web automation object and start an IE browser.
  • instantiate "Excel.Application" automation object and open XLS document.
  • get a phrase to be translated from Excel document.
  • navigate to Google translate tool web page.
  • fill out web controls (the text to be translated, source and target language combo-boxes) and then automatically press submit button.
  • wait the web page to load, get the translated text and save it into Excel document.

Let's see some code. I will skip the initial setup phase and go directly to web automation part which is more interesting:

for (var i = 1; i <= 5; ++i)
{
// Get current text to translate from XLS document.
var textToTranslate = excelApp.ActiveSheet.Cells(i, 1).Formula;

// Web automation part implemented with Twebst Automatio Studio.
browser.Navigate('http://www.google.com/language_tools?hl=en');
browser.FindElement('textarea', 'id=source').InputText(textToTranslate);
browser.FindElement('select', 'name=sl').Select('English');
browser.FindElement('select', 'name=tl').Select('French');
browser.FindElement('input submit', 'text=Translate').Click();

// Get the reszult and save it into XLS document.
var translatedText = browser.FindElement('span', 'id=result_box').text;
excelApp.ActiveSheet.Cells(i, 2).Formula = translatedText;
}

Points of interest:

  • FindElement method waits for the IE web page to be completely loaded before searching the element. The timeout value can be changed programatically (see loadTimeout, searchTimeout properties).
  • InputText method sets the specified value in edit/password web controls and fires all necessary HTML events (onkeydown, onkeypress, onkeyup, onchange, etc).
  • Select method fully automates combo-boxes and list-boxes web control and fires all necessary HTML events (like onchange).

Downloads:

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)

Friday, February 05, 2010

IE8 automation with Excel VBA macro

Twebst Automation Studio Ver 2.1 New Release!

Many people requested for VBA (Excel, Word) direct web automation support in Twebst Library. The previous version could not be easily used from VBA because of the way Excel works with methods with variable number of parameters (which is the case for many Twebst methods like: FindElement, FindBrowser, etc).

The new Twebst 2.1 version includes a new full set of interfaces and methods specially designed for Excel VBA. The web recorder was also updated to generate VBA code that you can easily include in your Excel macro. To see what's new in Twebst ver 2.1 check changelog.txt

Automating Internet Explorer from Excel VBA macro is now possible with Twebst Automation Studio 2.1.