selenium-vba: Automate the Web

Backstory

Cloud based web applications are amazing, they make life so much simpler. What they've done is effectively eliminated the numerous problems associated with deploying applications within a business. Now, it doesn't matter what operating system, configuration settings, etc. are installed on the machine, the cloud rains down a consistent user experience by simply using a compatible web browser. Awesome stuff, really.

Now, lets get back to the real world. The web was designed for documents and extended to include visual/audio media. Tack on some client side scripting and bang!: interaction and logical processing becomes feasible. All of the wonderful developments bring us awesome things like Facebook, Wordpress, Cloud9 IDE, and a whole slew of other amazing things. There's a catch, of course, and it has everything to do with bandwidth.

At work I have the great pleasure of using a cloud based business solution. This thing does everything we ask it to and more: manages inventory, tracks orders, costs, sales, provides messaging integrated with email, keeps employees in touch and updated, provides roles for "factory rat", "brown noser", and "whip cracker", etc. There are plenty of things this web application does but, there is one thing it can't do: make our internet connection faster. Yes, the simple answer is to just pay for a connection rated for a higher speed but, that really isn't an option. Our shop is located halfway between somewhere and somewhere else which puts us about two pastures and a swamp to the left of nowhere. Seriously, they've got campgrounds and cows out here, nobody is building a business class line through here in the foreseeable future.

Call it what you will, there are plenty of people who have given me an ear-full about how implementing cloud based solutions is a bad idea given limited throughput to the internet. I choose to look at it a different way, I see a company that's taking advantage of an easily implemented tool, that adds enough value to overlook the immediate and persistent annoyance of waiting for said tool to provide its (now semi-)immediate answers. Good for them I think, it is much easier to plop a laptop onto the web and point it to a URL than it is to form an IT department and reinvent the same solution poorly just to have it respond faster. So what do we do as employees to compensate for the sluggish web turning an awesome cloud based solution into crap? Automate.

selenium-vba: Automate the Web

What You're Here For

Given the situation described above I was immediately reminded of doing data entry on a shoddy machine that liked to lock up and freeze due to a lack of ram, processing speed, hard drive, etc. My solution then was to create an application to collect the data using very little resources and automate the data entry process using the resource intensive application during non business hours. The end result was that I could increase efficiency by several hundred percent and had time to add value in other ways, the same idea applies here.

It is fairly easy to create a windows form based application using Visual Studio. There are plenty of controls available in the toolbox that you just drag and drop onto your form in any position you like. Want a button to log you in to your favorite website? No problem, just drag a button onto the form, set it's text to "login", double click the button and edit the code to execute when the button is clicked in your app. Easy peasy. You can get a free copy of Visual Studio (Express) at http://www.microsoft.com/visualstudio/en-us/products/2010-editions/express

What is also incredibly easy is recording scripts to automate your favorite web browser by using the Selenium IDE addon for Firefox. After you've recorded a "test", you can export it into one of several programming languages. Out of the box, the Selenium IDE does not export scripts to Visual Basic but, with an extension it will. You can get the Selenium IDE at http://seleniumhq.org/projects/ide/ and you can get the extension to export scripts to VBA and VBS at http://code.google.com/p/selenium-vba/

What the selenium-vba wrapper will allow you to do is integrate browser automation with Microsoft Office products, or any other product using VBA. Also, selenium-vba will allow you to write standalone VB scripts to automate your browser. What I've been using it for is writing Windows Forms Applications with buttons that automate repetitive browser tasks related to a cloud based application in use at my workplace. The rest of this article is focused on setting up your first Windows Forms Application so you can make buttons that, with a single mouseclick, perform repetitive actions in your browser.

The Nitty Gritty

Environment

Firefox 12
.Net 3.5 SP1
.Net 4 Client Profile
.Net 4 Extended
(etc. lots of .Net)
selenium-vba SeleniumWrapper 1.0.5.1
Visual Basic Studio 2008 (also works with 2010)

Instructions

First, open the Visual Basic Studio and create a new Windows Forms Application. Next, add References in your project to all the .dll files installed by selenium-vba, they're located in the program files folder in a folder named "SeleniumWrapper". Then, add an existing item to your project, navigate to the SeleniumWrapper folder and select the .exe files. In the "solution explorer" view, select each of these added .exe files (ctrl click) and, with the files selected, switch to the properties view. In the properties view change the property "Copy to output directory" to "Copy if newer". Now add the license file from the SeleniumWrapper folder to your project and set it to be copied to the output directory as well. Rename it something smart like "SeleniumWrapperLicense.txt" or something, so people know what it applies to. Finally, open the project properties, advanced compiling options, and ensure that the target framework is set to .NET 3.5. You are now set up to use the selenium-vba in your Windows Forms Application!

Actually Writing a Visual Basic Program

OK, so this is a bit misleading, the Visual Studio writes most of the code based on drag and drop or other GUI assisted development. At any rate, you'll have to paste in or write some code at some point but, the Visual Studio keeps you writing code about how your application functions instead of tinkering around with writing button after button. So, first thing is first, open the toolbox view and drag a button from the toolbox and onto the form. Change the text property to whatever you want the text on the button to be, for now fill it in with "open google". Double click on your new button to have the Visual Studio automatically generate code to run when the button is clicked.

Tada! The code editor opened when you clicked on your button and the cursor should be inside the surboutine which will run when your button is clicked. Right now, the button won't do anything at all because there isn't any code inside the subroutine, let's fix that. First, you have to create an instance of the SeleniumWrapper.WebDriver class bundled up in one of those .dll files you referenced earlier. Scroll all the way to the top of your code and just after the class declaration on line 1, insert a second line that reads:

    Dim selenium As New SeleniumWrapper.WebDriver

Now you can use the methods from the selenium wrapper anywhere in your form to control a web browser. You'll want the browser to open when you start your application so you'll have to create an event handler for the Load event of your form.

At the top of the code editor there are two dropdown boxes placed side by side. In the dropdown on the left, select your form's events. In the dropdown on the right, select the event "Load". When you select the Load event from the dropdown box, a subroutine to handle the Load event will be added to your code and the cursor will be placed in the body of this subroutine. Whatever code you place in the body of this subroutine will be executed when the form is loaded.

Find the subroutine called Form_Load and paste this into it's body:

        selenium.start("firefox", "http://google.com")
        selenium.setTimeout("120000")
        selenium.setImplicitWait(5000)

That code will cause the Firefox web browser to be opened when you start your application. You can look up the other methods in the documentation (chm) included with the installation of the selenium-vba if you want to know exactly what they do.

Now, you want the web browser to close when you close your application. Create another event handler for the FormClosed event and paste this line into the body of the Form_FormClosed subroutine to cause the browser to close when you close your application:

        selenium.stop()

Alright, now that we've got the basics down your code should look like the following:

Public Class Form1
    Dim selenium As New SeleniumWrapper.WebDriver
    
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    End Sub

    Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        selenium.stop()
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        selenium.start("firefox", "http://google.com")
        selenium.setTimeout("120000")
        selenium.setImplicitWait(5000)
    End Sub
    
End Class

Go ahead and "start debugging" your application. An instance of your form will open up, the browser your form will control will open up, and if you click the button on your form nothing will happen because you haven't added any code to the button's click event handler. Close the form and notice that the web browser is closed for you automatically. Keeping the browser open while the form is open means that you can create as many buttons as you want to and chain several of them together to break up your workflow into logical segments acting on the same browser session (as in you'll be able to log in to some web app and do several useful things). You'll figure out what sort of useful stuff you want to do later, I may write an article about an automated login button later but, for now, lets get this one button we've got doing something to prove we're in control of the browser. Paste the following code into the button1_click subroutine:

        selenium.open("/")

Now "start debugging" your application again and notice that this time your button actually does something, it opens the google search page. Awesome!

You are now ready to compile your application and send it to your friends so they can see how awesome you are! Go ahead and build your project. When your project is finished building you'll have a fully functioning executable in the \bin\release directory inside your project's folder. By default your projects are stored in "My Documents\Visual Studio XXXX\projects Find your project and run the .exe you've created to verify it works then, zip up a copy of the release folder and email it to your buddies with the subject "I pwn teh webz" for more Glory than you can point your stylus at. . . . mouse at. . .touchpad. . . whatever. Just be sure they know that they'll need Firefox and the .Net Framework 3.5 installed on their machine in order for the program to work...

If you want to get really fancy and make sure your buddy only has to check that Firefox is installed, you can "publish" your project. This will create an installer that, well, installs your application onto computers. The published version of your application will add entries to your friends start menu for their clicking convenience and, before it installs itself it will check that the .Net Framework is installed on the machine. If it can't find the .Net Framework then it will automatically download and install the Framework. Awesome!

Gettin' Extra Super Fancy and Way Too Nerdy (it's easy)

So you've got the basic program written but you don't want to email your friends a silly application that only opens the google search page huh? Well, install the Selenium IDE into your Firefox, install the VBA/VBS formatter (xpi, from file) into your firefox, turn on the "experimental features" in selenium (then close and restart seleniumIDE) and... Record things you're doing on the web with selenium IDE. Run the "test" you recorded and when it's just the way you want it, click options->formatter->VBA(WebDriver backed), switch the code editor from "table" to "source" tab and copy the relevant bits of code from the selenium IDE into the click handler of one of your many fancy buttons on your application (note: you will have to add parenthesis and do some very minor edits, the debugger will tell you what isn't quite right). Eazy peazy.

Pro Tips

Dim someNumber As String = InputBox("Gimmie a Number!")

The input box will ask for a number. someNumber will contain the number typed into the input box or, blank if nothing entered or, blank if input box is closed or cancelled. Use input boxes to get information from users as part of the automation process.

MsgBox(someNumber)

The message box will display a message. In this case it will display whatever you've stored in someNumber, try combining the InputBox and MsgBox to get a feel for how they work.

Change the line:

        selenium.start("firefox", "http://google.com")

        selenium.start("ie", "http://google.com")

and see what happens

Super Pro Tips

Get your head around the code in the examples provided with the selenium-vba SeleniumWrapper and do super fancy things with Word and Excel. Bonus points for posting a working example of Selenium-VBA reading and writing in Access. Let the developer of selenium-vba, Florent Breheret, know if you get an Access example together, it would be good to have one included in the installed examples.

Uber Geek Pointers

Write a massive article articulating the obvious uses of selenium-vba with step by step instructions so that those of us to whom said obviousness remains obscure may have an "Aha" moment and be all like "Dude, this is badass!" No really, I'm just hacking at getting things to work here, I'd like more use cases for selenium-vba posted because I think it's really really cool being able to integrate Selenium into every nook and crevice of Windows like this. I've noticed that the C# Api Docs (3.5) for Selenium give more details about the methods selenium-vba wraps and builds upon. The C# Api Docs, also referred to as .Net Api are available online or as a chm file with the download at http://seleniumhq.org/download/

Matthew Kastor

Labels