Sunday, December 01, 2013

Using Selenium with Excel VBA

We had to change the names of 700 students in the student information system that I'm the product manager for at work. Instead of requesting that our support team write a database script, I figured it would be a good opportunity to try using Selenium (http://www.seleniumhq.org to rename the students using the browser user interface. In the past I have written VBA (Visual Basic for Applications) scripts to control Internet Explorer and I wanted to extend that approach to control Firefox and/or Chrome.

Excel VBA provides a very capable environment and the spreadsheet as an underlying model works very well for a class of problems. In this case we have a list of students already in the system and I added a couple of columns to contain the new first and last names. A simple loop through the rows using Selenium calls to manipulate the user interface and then record the results back in the spreadsheet is a straight-forward VBA macro.

Of course, without a VBA wrapper for Selenium this project would not go anywhere. I'd like to thank Florent Breheret for creating a wrapper and making it available as open source:

Viewing the source for the wrappers is important because some Selenium features are not implemented and other, non-standard, but helpful features are available (WindowHandles).

Selenium IDE for Firefox was invaluable. For my purposes, it's a script snippet recording solution that you then have to build your full scripts around using your environment of choice.

Stack Overflow was a helpful repository of answers for questions related to Selenium.

I ran into a problem with model dialogs, and ultimately didn't have to solve it because the script would complete what it needed to do with a simple On Error Resume Next. I may dig into this more at a later date. If I really get ambitious then I'll figure out how to edit/build the wrapper (requires C#).

After some debugging of the VBA/Selenium script (at the staging site first, and then with the production site), I was able to accomplish the renaming of 700 students without tedious manual effort. Creating Selenium scripts requires sleuthing the UI for the system you're trying to automate using Selenium IDE and the browser's tools. Of course if you already know the website and how it is coded it will be easier. If you have a site with frames and especially popups, you can expect it to take some time to have a script that works well. Also, plan to spend time making the script less fragile and smart about checking to make sure it is changing what you expect. For example, when changing the student names, I made sure to check the field that verified I was in the expected school district.

I did encounter one bug where the script would timeout. This happened after about four iterations of renaming students. The solution was to have the script logout of the system and then login every four renames. Definitely undesirable and if this continues to come up on future scripts I'll have to dig in more, but probably not without digging into C#.

I've just scratched the surface with Selenium for automation and of course it has great potential use for website testing. A future project will be to blend it with XCode to experiment with browser automation / testing from a mobile platform.


Jhon Mick said...

I went through your blog, it’s totally awesome. Being most popular automation testing tool, selenium is used in automation software testing process.
Selenium Training in Chennai | Best Selenium training institute in Chennai


Thanks For Your valuable posting, it was very informative
Webdesign L├╝denscheid