Move data from Excel to IE and back with VBA web macros

Let's continue my previous post (IE8 Automation using Excel VBA Macro) with some VBA Internet Explorer automation code:

' Don't forget to add reference to TwebstLib from menu Tools/References
Dim core As TwebstLib.ICoreVba
Set core = New TwebstLib.core

Dim browser As TwebstLib.IBrowserVba
Set browser = core.StartBrowserVba("http://www.google.com/")

For i = 1 To 5
   ' Get the current text to be translated
   Dim textToTranslate As String
   textToTranslate = ActiveSheet.Cells(i, 1).Formula

   ' Twebst web automation part
   Call browser.Navigate("http://www.google.com/language_tools?hl=en")
   Call browser.FindElementVba("textarea", "id=source").InputText(textToTranslate)
   Call browser.FindElementVba("select", "name=sl").Select("English")
   Call browser.FindElementVba("select", "name=tl").Select("French")
   Call browser.FindElementVba("input submit", "text=Translate").Click

   ' Find result span object
   Dim resultObj As TwebstLib.IElementVba
   Set resultObj = browser.FindElementVba("span", "id=result_box")

   ' Get the text result
   Dim translatedText As String
   translatedText = resultObj.Text

   ' Save the result into Excel sheet
   ActiveSheet.Cells(i, 2).Formula = translatedText
Next

Points of interest:

• in VBA web macro new methods are used (see FindElementVba). The newly added methods (from version 2.1) are especially designed for VBA language and they work the same as they non-VBA counterparts except the fact no variable number of search conditions are supported.
• Twebst web recorder was updated to automatically generate VBA macro code against the newly added methods.
• all web page loading synchronization is provided by default by the library.
• when automating web controls all necessary HTML events are fired up against target object (see fireEvent) to simulate user's web action.
  

Downloads:

• Twebst Automation Studio PRO you'll need this installed before running the sample VBA macro
• TwebstVBA.zip containing sample Excel and BAS files.

IE8 Automation using Excel VBA Macro

In my previous posts (Excel and IE automation - the WSH way, Extract web data into Excel.Application with browser macro) I automated both IE and Excel using WSH. Now I'm going to automate Internet Explorer directly from an Excel macro using the VBA language.

The VBA macro will work against a sheet containing phrases to be translated using Google Translate tool. It automatically gets each phrase, opens IE browser, navigates to translation page, waits the web page to complete loading, fills out the text to be translated into HTML web controls, press submit button, gets back the result and saves it into the same Excel sheet.

It is possible to directly automate IE browser from VBA macro but it is not always as simple as it should be. (see my older post: What's wrong with Internet Explorer Automation?) Using Twebst Automation Studio when automating IE from VBA has some advangates:

• it works with all Internet Explorer versions: IE6, IE7, IE8 and with IE protected mode.


• it works on all Windows versions: XP, Vista, Win7


• easily search of elements across all sub-documents inside frames/iframes hierarchy


• search HTML elements using regular expressions


• simulate user actions on all HTML controls (button, combo-box, list-box, edit-box, upload control) and fire all necessary HTML events


• auto-generate VBA code with powerful web recorder


• much more (see Twebst Automation Studio website)

That's all for now. Until our next encounter, check the download section below and have fun with the Excel VBA macro code.

Downloads:

• Twebst Automation Studio PRO this web automation tool is used in VBA maro so you have to install it first.


• TwebstVBA.zip containing sample XLS and BAS files.

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.

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:

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)

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.

Get It While it's Hot!


.

How to automate existing instances of Internet Explorer - Tutorial(4)

As explained in a previous tutorial, a web macro can start by connecting to an already running instance of Internet Explorer browser. A web macro usually starts a new Internet Explorer page and then navigates to a URL to begin with. There are times when automating an existing IE window is a more natural alternative. Let's start with a JScript/WSH simple web macro:

// Create core object.
var core = new ActiveXObject("Twebst.Core");

// Find a browser for which the displayed URL contains "yahoo".
core.useRegExp = true;
var b1 = core.FindBrowser("url=.*yahoo.*");
b1.Navigate("www.google.com");

// Find a browser for which the title is "Google" (exact match).
core.useRegExp = false;
var b2 = core.FindBrowser("title=Google");

b2.Close();

Basically FindBrowser method searches for a browser page based on TITLE and/or URL. If core.useRegExp is false then an exact search is used. If core.useRegExp is true then the search uses regular expressions. Once connected to a IE browser instance, you get a Browser object to play with.

Of course, this works for IE6, IE7, IE8 on Windows XP, Vista and Windows 7 (32 and 64 bit) and it's compatible with Internet Explorer Protected Mode.

What can you do with a Browser object? Quite a lot of things: Navigate to a URL, FindElement and FindFrame inside it, Close the browser, get information about the browser, automate modal and modeless HTML dialgos and wait to complete navigation and loading.

Enough talk! It's time to Download Twebst Automation Studio and see it for yourself.

.

IE macro - how to get programmatic control over HTML elements - Tutorial(3)

Finding elements inside HTML frames/iframes hierarchy with Twebst Web Automation Library

Once you have started a web macro and you've got a Browser object to play with, it's time to get programmatic access HTML controls inside the web page. As any web developer should know, you can easily find elements inside a document using document.getElementById.

In the world of web automation things are a bit different; web macros need to access HTML elements in any web document and at any level of DOM hierarchy. The target HTML element can be inside frames/iframes loaded from various domains, subject of cross-frame scripting security restrictions (see my older posts: "When IHTMLWindow2::get_document returns E_ACCESSDENIED" and "When IHTMLWindow2.document throws UnauthorizedAccessException").

Here's a simple VBscript web macro that automates a Google search in Internet Explorer:

Dim core
Dim browser
Set core    = CreateObject("Twebst.Core")
Set browser = core.StartBrowser("http://www.google.com/")

Call browser.FindElement("input text",   "name=q").InputText("codecentrix")
Call browser.FindElement("input submit", "name=btnG").Click()

What is so great about the code above is the way FindElement method works: first it waits for the browser to load the HTML document then it searches for the element thru all frame/iframe hierarchy.

Once an Element is found you can perform actions on it like: Click, RightClick, InputText, Check, Uncheck, Select, Highlight etc. What is even cooler than this, is that you don't have to write these statements by yourself. They will be automatically generated by Twebst Web Recorder which you can get for free:

Get Free Web Recorder for Internet Explorer

.

IE8 automation: How to programatically open an URL - Tutorial (2)

Get started with web macros in IE

Every web macro has to start somewhere, has to start somehow. There are basically two scenarios of automating Internet Explorer browser:

• start a new IE browser and navigate to a URL to begin with
  


• connect to an existing instance of Internet Explorer browser and continue automation

In this short tutorial I'll show you how to open a new Internet Explorer browser and open an URL with Twebst Automation Studio. Let's start with a short JScript web macro.

var core    = new ActiveXObject('Twebst.Core');
var browser = core.StartBrowser('http://www.google.com/');

The code is quite self-explaining. It creates a Twebst object and then opens a given URL in a new IE browser instance. What you get back from StartBrowser call is a Browser object that can be used to further automate Internet Explorer.

What can you do with a browser object? Quite a lot of things: navigate to a URL, find HTML elements and frames inside it, close the browser, get information about the browser, automate modal and modeless HTML dialgos and wait to complete navigation and loading.

That's all for now, but these feature will be covered in next tutorials!

Get Twebst Automation Studio Free Edition

(Automation Library and Macro Recorder for Internet Explorer)

Automate HTML File Upload Control - Tutorial (1)

If you ever tried to programatically change the value of a HTML File Upload Control you probably noticed that <input type="file" /> element is read-only! This happens for good security reasons: web pages scripts should not be able to upload random files without user consent. Unfortunatelly the control is read-only for browser extensions and add-ons too (BHO, toolbars, side bars, etc).

For IE6 and IE7 the upload control may be automated by setting the focus to "Internet Explorer_Server" window and generating Win32 keyboard events. For IE8, the upload control is read-only, the user can only set a value by pressing "Browse" button and choosing a file.

With Twebst Automation Studio, automating HTML File Upload Control can't be easier. Here's a short VBScript sample of doing it:

Option Explicit
Dim core
Dim browser
Set core = CreateObject("Twebst.Core")
Set browser = core.StartBrowser("http://www.2shared.com/")

Call browser.FindElement("input file", "id=upField").InputText("C:\somepath\photo1.jpg")

Get Twebst Automation Studio Free Edition!

New Web Macro Recorder released!

After several months of complete radio silence, things have been moving forward for Twebst Library which turned into a more powerful and mature product.
Please welcome Twebst Web Automation Studio!

Twebst Automation Studio is an advanced web automation framework for Internet Explorer that can be used within any environment that supports COM, from scripting languages (JScript, VBScript) to high level programming languages (C#, VB.Net, C++).

The framework includes two components: Twebst Web Recorder that automatically generates most of the automation code and Twebst Library which is a collection of programable objects that creates a web automation API.

But one image is better than thousand words...

Twebst Web Recorder features:

• Easily create web macros through an intuitive graphical interface.
• Generate web automation code using the language of your choice: JScript, VBScript, C#, VB.Net, C++
• Record web actions on all HTML controls (button, combo-box, list-box, edit-box)

And one more thing: FREE version available!

Download Twebst Automation Studio

.