My favorites | Sign in
Project Home Issues Source
Project Information
Members
Links

Selenium VBA is a Windows COM library that uses the popular Selenium web testing tool.
It makes it possible to automate web browsing using Excel and VBA code or using a simple VBS file executed by double-clicking on it.
User's actions can be recorded using the Firefox plugin named "Selenium IDE" and translated to VBA or VBS with the provided formatters.

It comes usefull to :

  • Automate repetitive web browser tasks.
  • Quickly fill a web form multiple times with an Excel data set.
  • Extract data from a web page in an Excel sheet.
  • Run web tests against an Excel data set (Data-Driven Testing).
  • Take screenshots of a Web site and save them in a PDF file.
  • Use the selenium automation framework within QTP (Quick Test Pro).
  • Compare the rendering of two web pages to quickly detect regressions
  • Measure the page loading time as well as the server response time.

This project provides :

  • A COM library to use Selenium with Excel in the Visual Basic Editor or within a visual basic script (VBS).
  • The Selenium IDE plugin with new formatters to convert recorded actions to the VBA/VBS programming language.
  • All the Selenium 2 and Selenium 1(RC) commands.
  • A console application to run scripts
  • A simple and quick way to drive Firefox, IE, Chrome, Safari and PhantomJS (headless webkit).

Download

Documentation

There are multiple ways to get documentation:

  • From the Selenium website with samples referring to the CShap language (Most of the commands have same syntax)
  • From the provided help file : Programs>SeleniumWrapper>API Documentation
  • Using the object browser in the VBA editor (F2)
  • From the web with the keyword "Selenium CShap" followed by your issue

Minimum Requirements

How to create/record a VBA script ?

  • Launch Firefox and Selenium IDE (Ctrl+Alt+S)
  • Open or record a script
  • Click on Menu "Option>Format>VBA", select the Tab "Source" and copy the text
  • Or Click on Menu "File>Export Test Case As>VBA" and save the file
  • Paste the generated script in a module in Excel VBA (Alt + F11)

How to run a VBA script in Excel with Visual Basic for Application ?

  • Open a new workbook or the provided template (All Programs/SeleniumWrapper/Excel Template)
  • Click on Menu "Tools>Macro>Visual Basic Editor"
  • Add the reference "SeleniumWraper Type Library" in Tools>References (Already present in the template)
  • In VBE, click on menu "Insert>Module" and paste your code
  • Click on Run in VBE or Run the macro from Excel

How to select the browser to work with?

This application works with real browsers, which makes it possible to interact with dynamic contents created by Javascript. The command to lauch a browser requires le name of the browser and the root of the web site.

Here is an example which launches firefox, opens the science page of Yahoo news, the politics page and closes the browser :

Public Sub TC_Browsers()
   Dim driver As New SeleniumWrapper.WebDriver
   driver.start "firefox", "http://news.yahoo.com"
   driver.open "/science"
   driver.open "/politics"
   driver.stop
End Sub

There are 4 browsers that can be controlled locally that need to be installed to work with:

  • Firefox : "firefox"
  • Chrome : "chrome"
  • InternetExplorer : "ie"
  • InternetExplorer 64 bits : "ie64"
  • Safari : "safari"
There is also one headless browser(PhantomJS) included in the installation which behave like a browser but without a window to interect with:
  • PhantomJS : "phantomjs"

Another feature is the possibility to control a browser remotely using the the remote web driver server: https://code.google.com/p/selenium/wiki/RemoteWebDriverServer

Here is a example to remotely control Firefox on the station 192.168.0.26 :
Public Sub TC_Browsers()
   Dim driver As New SeleniumWrapper.WebDriver
   driver.startRemotely "firefox", "http://192.168.0.26:4444/wd/hub" "http://news.yahoo.com"
   driver.open "/science"
   ...
End Sub

How to get an element locator ?

Solution 1 :

Using Selenium IDE, copy the recorded selector in the field "Target".
This selector can then be used as follow :
selenium.click "target"
selenium.type "target", "your text"
For the a Selenium 2 command, remove the prefix of the target and use the corresponding command:

  • Target "css=value" : driver.findElementByCssSelector("value").sendKeys "your text"
  • Target "xpath=value" : driver.findElementByXPath("value").sendKeys "your text"
  • Target "name=value" : driver.findElementByName("value").sendKeys "your text"
  • Target "id=value" : driver.findElementById("value").sendKeys "your text"
  • Target "link=value" : driver.findElementByLinkText("value").sendKeys "your text"

Solution 2 :

In Firefox, right click on the element and click on inspect element
Right click one the node and click "Copy Unique Selector"
Then to use the selector:
selenium.click "css=selector"
driver.findElementByCssSelector("selector").click

Excel script example: Web automation

This example runs a simple web search and pastes a screenshot of the result in a worksheet:
Public Sub TC001()
   Dim selenium As New SeleniumWrapper.WebDriver
   selenium.start "firefox", "http://www.google.com/"
   selenium.open "/"
   selenium.type "name=q", "Eiffel tower"
   selenium.click "name=btnG"
   selenium.wait 1000
   selenium.getScreenshot().copy
   selenium.stop
   Sheets(1).Range("A10").PasteSpecial       'Paste the screenshoot at range A10
End Sub

Excel script example: Data driven testing

In this example, a worksheet contains a list of Urls and expected titles. Each Url is oppend in Firefox, then the web page title is compared with the expected one and the result is sent back to the worksheet.
Public Sub TC002()
    Dim selenium As New SeleniumWrapper.WebDriver, r As Range
    selenium.Start "firefox", "http://www.google.com"       'Launch Firefox
    For Each r In Range("MyValues").Rows                    'Loop for each row in the range named "MyValues"
        selenium.open r.Cells(1, 1).Text                    'open the link defined in the first column of "MyValues"
        selenium.waitForNotTitle ""                         'wait for the title to load
        r.Cells(1, 3) = selenium.verifyTitle(r.Cells(1, 2))   'Compare the page title with the second column and write the verification result in the third column
    Next
    selenium.stop
End Sub
This wrapper implements assertion and verification commands. When an assert fails (ex: assertTitle), an error popup appears and the execution is stopped. A verification command (ex: verifyTitle) just returns a string with the test result without breaking.

VBS Script example : Create a PDF of screenshots

This scrip will help you to quickly inspect a list of web pages without having to navigate the website and wait for page loading. It automatically opens the webpage, takes a screenshot and saves it in a Pdf file:
Set selenium = CreateObject("SeleniumWrapper.WebDriver")
Set pdf = CreateObject("SeleniumWrapper.PdfFile")
selenium.start "firefox", "http://www.google.com"
selenium.open "search?q=eiffel+tower"
pdf.addImage selenium.getScreenshot(), "Google search - Eiffel tower"
selenium.open "http://maps.google.com/maps?q=eiffel+tower"
pdf.addImage selenium.getScreenshot(), "Google map - Eiffel tower"
pdf.saveAs "c:\selenium-capture.pdf"
selenium.stop

Excel script example: Web scraping (From version 1.0.18)

This example gets all the world market indexes in the first worksheet and all the top stories tiltles in the second one:
Public Sub TC003()
    Dim driver As New SeleniumWrapper.WebDriver
    driver.Start "chrome", "https://www.google.co.uk"  'Starts the browser
    driver.Open "/finance"  'Opens the finance page
    
    Dim data1, data2
    data1 = driver.findElementByCssSelector("#markets table").AsTable.GetData() 'Gets the world market indexes from the table
    Sheet1.[A1].Resize(UBound(data2, 1), UBound(data2, 2)).Value = data1  'Writes the collected data in the first worksheet
    data2 = driver.findElementsByCssSelector("#market-news-stream a.title").GetData()   'Gets the top stories titles
    Sheet2.[A1].Resize(UBound(data2)).Value = data2 'Writes the collected data in the second worksheet
    
    driver.stop 'Stops the browser
End Sub

VBS Script example : Compare the rendering of two web pages (From version 1.0.18)

Getting tired playing "Where is Wally?" for each delivery ?
Here is an example to quickly identify changes between two versions of a web page :
Set driver = CreateObject("SeleniumWrapper.WebDriver")
driver.start "firefox", "http://www.google.co.uk" 'Starts the browser
driver.open "/" 'Opens google search version UK
Set imageA = driver.getScreenshot() 'Captures the rendering
driver.open "http://www.google.fr" 'Opens Google search version FR
Set imageB = driver.getScreenshot()  'Captures the rendering
driver.stop 'Stops the browser
imageA.compareTo(imageB).saveAs "diff.png" 'Compares images and saves the result
For result, an image showing differences with a non-black colour :

Excel script example : Measure real loading times (From version 1.0.18)

The getPerformanceTiming command returns latency-related performance informations :

  • Page load : Total page load delay experienced by the user
  • Redirect : Time taken for page redirection
  • DNS : time taken to perform DNS lookup to the server
  • Connecting : time taken to connect to the server
  • Waiting : time taken for the server to start responding
  • Receiving : time taken to receive the data from the server
  • DOM : Time spent building the DOM
  • Events : Time taken to handle onLoad event
This example navigates through Yahoo news and writes the metrics for each web page in an Excel WorkSheet:

Function NextRow() As Range  
    Static i As Integer
    i = IIf(i = 0, 1, i + 1)	'Increments the row index
    Set NextRow = Range("A:I").Rows(i)	'Returns the next row
End Function

Public Sub GetPerformanceTiming()
    Dim driver As New SeleniumWrapper.WebDriver
    driver.Start "firefox", "http://uk.news.yahoo.com"	'Starts firefox
    
    'Writes the titles on the first row
    NextRow() = Array("Page Url", "Page load", "Redirect", "DNS", "Connecting", "Waiting", "Receiving", "DOM", "Events")
    
    'Opens each page and copies the metrics in the WorkSheet ( 1 row for each page opened)
    driver.Open "tech"
      NextRow() = driver.getPerformanceTiming()
    driver.Open "world"
      NextRow() = driver.getPerformanceTiming()
    driver.Open "opinion"
      NextRow() = driver.getPerformanceTiming()
    driver.Open "business"
      NextRow() = driver.getPerformanceTiming()
    
    driver.stop	'Closes Firefox
End Sub

Third-party software components included in the installation package

Tested environments

  • Win7 / Excel 2010/ Firefox 11 / Selenium IDE 1.7.1
  • WinXP / Excel 2003 / Firefox 10 / Selenium IDE 1.6.0

Author

Florent BREHERET
Powered by Google Project Hosting