How To Automate Firefox Or Chrome With Excel VBA And Selenium
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, and prioritizing goals. Last week, I described how to automate IE 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.10 Useful Excel Project Management Templates for Tracking 10 Useful Excel Project Management Templates for TrackingProject management templates can help you replicate successful projects. Here are the essential Microsoft Excel templates for you.READ MORE
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, 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”.Using VBA to Automate Internet Explorer Sessions From an Excel Spreadsheet Using VBA to Automate Internet Explorer Sessions From an Excel SpreadsheetIts 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
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).
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.
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.
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.
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.
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.
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.
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.
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!
Arquitecto especialista en gestion de proyectos si necesitas desarrollar algun proyecto en Bogota contactame en el 3006825874 o visita mi pagina en www.arquitectobogota.tk
0 comentarios:
Publicar un comentario