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:

• Twebst Automation Studio - you need to install this web automation tool before running the demo WSH script below.
• ExcelToWebMacro.zip - web scraping demo script that saves results into Excel file.