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.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).


No comments: