Pinterest Stumbleupon Whatsapp
Ads by Google

The key to automation is doing things faster and easier than you’ve ever done before. So what takes you a long time to do? What part of your workload would like to automate?

When it comes to productivity, Excel is the most common tool used by people for things like organizing finances, managing projects 15 Useful Excel Templates for Project Management & Tracking 15 Useful Excel Templates for Project Management & Tracking Read More , and prioritizing goals Use Excel & Google Tasks To Create The Best Goals Management Tool Ever Use Excel & Google Tasks To Create The Best Goals Management Tool Ever Lately, I've started to realize that I'm working very hard, but if someone asked me whether or not I feel that I'm working toward a larger purpose, I'd have to say that I really don't... Read More . Last week, I described how to automate IE Using VBA To Automate Internet Explorer Sessions From An Excel Spreadsheet Using VBA To Automate Internet Explorer Sessions From An Excel Spreadsheet Its integration with Windows allows control of Internet Explorer in a number of surprising ways using Visual Basic for Applications (VBA) script from any application that supports it, such as Word, Outlook or Excel. Read More by setting up groups of URLs in Excel sheets, and then using the IE object in Windows to automate launching groups of URLs straight from inside Excel.

Following that article, many readers  asked how to do the same thing in Firefox or Chrome. Since neither Firefox nor Chrome have a similar object included with Windows that can be used for VBA automation, performing the same sort of actions on those other browsers requires an extra step. That step is the installation of Selenium VBA – a Windows COM wrapper for Selenium.

It allows you to perform calls from any application that supports VBA in order to open a browser – not just IE – and control that browser session. It’s a powerful automation tool that is so commonly used that many popular browser vendors are incorporating Selenium as a native part of their browser. Using it in your automation applications is sure to be supported for years to come.

Automating Firefox and Chrome with Selenium

Before you get started with this project, you’ll need to download and install the Selenium VBA wrapper. Then, just as I discussed in the article on automating IE Using VBA To Automate Internet Explorer Sessions From An Excel Spreadsheet Using VBA To Automate Internet Explorer Sessions From An Excel Spreadsheet Its integration with Windows allows control of Internet Explorer in a number of surprising ways using Visual Basic for Applications (VBA) script from any application that supports it, such as Word, Outlook or Excel. Read More , to write any VBA in Excel, you need to get into design mode and view the code. If you’ve never done this before, you just need to go into the “Developer” menu item, and click on Design Mode. Click on the “Insert” button, click on the ActiveX button and draw it onto your sheet somewhere. Once you’ve done that, click on the button and then click on “View Code”.

selenium1

Ads by Google

In the code editor window, at the lower left, make sure to change both the “Name” and the “Caption” to reflect what the button is for. In this case, the button will be to open up the list of URLs that you’ve listed in the spreadsheet. In my case, I just called it cmdLoadURLs, and made the caption “Load URLs” (this is what shows up on the button).

selenium1b

Next, you need to enable the Selenium Wrapper by clicking on the Tools menu, then click on “References”, and then browse down to the reference called “SeleniumWrapper Type Library”. Check that reference box and then click OK.

selenium2

You’re now ready to start writing browser automation code using the Selenium Wrapper!

Functionality of the Selenium Wrapper

The Selenium VBA Wrapper gives you a lot more functionality than I’ll be able to show you in this one article. You can see just how much is available beyond just the WebDriver, by defining the Selenium object as “New SeleniumWrapper”. When you type the period, it’ll drop down all of the elements of the types of objects you can control, like browser images, PDF files, keyboard keys, and more.

selenium3

This example code will be using the WebDriver. Once you use the Selenium WebDriver object in your code and type the period, it’ll drop down a very long list of methods and properties that you can use to automate your web browser.

selenium4

It can take some time to learn everything that’s available, but Google can turn up some good examples, behind the samples available at the Google Code page. Unfortunately, as far as I know, there’s no straightforward guide out there for using Selenium, but I would be grateful of any readers that could provide any resources!

Writing Your VBA Selenium Code

In this example, like in the IE article, I’ve created a list of URLs that I want to open in Firefox automatically. I then created the button as described in the first part of this article.

selenium5

The code behind the button is straightforward, but I’m going to explain what each section does. First off, you need to launch the Firefox application. The “selenium.start” method will do that. The setTimeout and setImplicitWait methods are not critical, but can prevent your application from locking up forever if the browser doesn’t respond for some reason.

Dim selenium As New SeleniumWrapper.WebDriver
Dim FF As Object
Dim intRowPosition As Integer
Dim keys As New SeleniumWrapper.keys

selenium.Start "firefox", "http://www.google.com"
selenium.setTimeout ("120000")
selenium.setImplicitWait (5000)
intRowPosition = 2
selenium.Open Sheet1.Range("A" & intRowPosition)

The last couple of lines set the row of the Excel data to read (row two, where the first URL is stored in the spreadsheet), and then performs the “selenium.open” method to read the URL from that spreadsheet, and open it in Firefox.

selenium7

The next section of code increments the row pointer and reads the next URL in the list. If the cell isn’t blank, it then uses the SendKeys method to launch a new tab in Firefox, reads the next URL from that sell, and opens the URL in that new tab.

intRowPosition = intRowPosition + 1

While Sheet1.Range("A" & intRowPosition) <> vbNullString
    selenium.SendKeys keys.Control & "t"
    selenium.Open Sheet1.Range("A" & intRowPosition)
    intRowPosition = intRowPosition + 1
Wend

Set IE = Nothing

The script will loop through your entire list until all URLs are open in their own tabs. Here’s the browser after the loop went through a second time and opened MUO in a new tab.

selenium8

If you want to use this code for Chrome, all you need to do is change the line “selenium.Start “firefox”” to “chrome” instead.

Now Create Your Own Script

As I mentioned above, the best way to learn the power of the Selenium Wrapper is to type “selenium” in your code, hit the period and just browse down the very long list of available properties and methods. For example, you can use the .URL property to obtain the URL of the currently open tab in Firefox or Chrome.

selenium9

As you can see, there are many more things you can do that go way beyond the scope of this article. But experiment and have some fun. Thankfully, most of the functions will pop-up help text that shows you what parameters each function expects. That can help a great deal when it comes to figuring out how to use it.

Do you use VBA to automate your computer? Have you ever automated your browser sessions from VBA? Share your own tips and experiences with it in the comments section below!

  1. Revanth
    May 21, 2016 at 8:25 am

    Hi
    In my excel vba Selinium wrapper is not showing to enable the option please let me know the option to do it.

  2. Nikhil Joseph
    June 30, 2015 at 7:10 pm

    Firstly let me thank you for this article. This has really helped me progess in my career.

    Second, I had a doubt. I am trying to stimulate Ctrl+v using the selenium VBA wrapper on chrome but haven't been successful till now. Could you please share an example on this.

    Once again thank you for all the help.

  3. Swa
    May 4, 2015 at 9:48 am

    I am not able to find selenium wrapper type library in my excel 2010. Could you please help me how to get this?

  4. Harvey
    January 5, 2015 at 5:59 pm

    Hi, much interesting presentation,
    I would like to save an article of a webpage read under Chrome into a Word file and save the URL an title of the article in an Excel file. The procedure should be called from the webpage (best as a Chrome extension).
    Could Selenium VBA wrapper help ?
    Thanks

  5. Jonathan
    December 30, 2014 at 3:23 pm

    It seems that it is not working for me. Does anyone have the update code or code snippet for me to try out? Thanks alot.

  6. David
    April 3, 2014 at 9:46 pm

    If one is just using VBScript outside Excel, there is another option as well:

    http://htejera.users.sourceforge.net/vbswebdriver/

    that does require running an instance of RemoteWebDriver/Selenium server though compared to the VBA solution.

  7. mehow
    February 13, 2014 at 4:12 pm

    Ryan, this is a great article and I'm sure lots of people will find it very useful :)

    Regards,
    mehow

    http://vba4all.wordpress.com

  8. chris
    October 25, 2013 at 12:02 pm

    If anyone is having trouble with the VBA, there's a great litle tutorial here http://www.visualbasictutorial.net

  9. GeorgeJ
    October 23, 2013 at 3:43 pm

    This looks like what i'm looking for - but I could be wrong. I have been trying to find a way to extract some html links from a Chrome popup (using the 'Save to Google Drive' extension). Can Selenium extract from an already open browser window, or would it only be able to do so for browser windows it has opened and assigned?

    Either way it looks promising and thanks for the informative write-up.

  10. miaousse
    October 19, 2013 at 10:00 am

    hi
    why using vba (excel or word or what ever office application) when using vbscript or wsh do the job?

    • Ryan D
      October 19, 2013 at 4:14 pm

      I'm sorry - I don't quite follow what you're asking. Could you rephrase?

    • TechnoAngina
      October 23, 2013 at 5:12 pm

      I think that he's asking why you chose to use VBA inside of Excel when plain VBA would do the same thing with less resource overhead. As in what are the benefits.

  11. ram verma
    October 17, 2013 at 5:50 pm

    this is a very good post

  12. Scroogled
    October 17, 2013 at 5:19 pm

    Amazing now on Firefox. I tried IE version of this few days ago.

Leave a Reply

Your email address will not be published. Required fields are marked *