Sunday, March 07, 2010

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:

4 comments:

Anonymous said...

Nice post and this mail helped me alot in my college assignement. Gratefulness you on your information.

Anonymous said...

Nice fill someone in on and this fill someone in on helped me alot in my college assignement. Gratefulness you for your information.

Mithat said...

this is (imho) the way to go.

agustian zhuang said...

Fantastic Product =) make life much more easier...