THIS PROJECT HAS NOW MIGRATED TO GITHUB:

http://florentbr.github.io/SeleniumBasic

(This website is no longer supported and the remaining information is for historical purposes only.)

#

#

#

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

• Latest version
• Change Log

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

• Windows XP
  
• Microsoft Office 2003/2007/2010/2013 with option "VBA For Application" installed [Update required for Office 2003]
  
• Mozilla Firefox 8 to 34 [Download Page]
  
• Microsoft .Net Framework 3.5 SP1 [Download Page]

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"

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 ?

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"

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

• .NET Selenium Client Drivers [Home page]
  
• IE driver server [Home page]
  
• Chrome driver server [Home page]
  
• PhantomJS server [Home page]
  
• PDFsharp library [Home page]

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