A History of Connecting Excel To The Web
Although this article deals primarily with the extensive benefits of using Selenium with Excel, there's also a rich history in Excel's journey to connecting to the web.
Excel has had the ability to connect to the internet for a long time, but it's come a long way in the tools and ease of use (and usefulness). Web Queries were introduced with Excel 97, which was great for web scraping after a bit of setup was done. You can create these connection strings in VBA or utilize a web query ".iqy" file after setup.
This was replaced in 2010 with a much more powerful tool, Excel's Power Query. Power Query can scrape and refresh online data, but it doesn't allow you to control a website.
Unfortunately, neither of these methods allow you to boot up a web browser, log in, click a button based on programming intelligence or fill out an online form using your spreadsheet data.
Excel Web Query
Web query was one of the first ways we were able to grab data from the web and have it input into Excel sheets. Many people began their Excel - Web journey by downloading Yahoo Finance stock tickers and experimenting with automating this experience using variables and advanced VBA codes.
Microsoft described the Web Query when first came out as
A Web query is a new feature in Microsoft Excel 97 that allows you to retrieve data stored on an intranet, the Internet, or the World Wide Web. A Web query can use static parameters, dynamic parameters, or a combination of both. Queries with static parameters send a query without any input; queries with dynamic parameters prompt you for input. Regardless of the type of parameters in the query, the requested information is pulled from an Internet or an intranet site, and the results are placed in a worksheet.
Using a Hypertext Markup Language (HTML) form, you can use two methods to send parameters to the server: GET and POST. Use GET when you are sending small amounts of information and POST when you are sending larger amounts of information. The GET method appends the parameters to the Uniform Resource Locator (URL). The POST method sends the parameters as a separate line of text in the query file.
Microsoft KB Archive/157482 XL97: How to Create Web Query (.iqy) Files
Power Query
Microsoft Power Query is an excellent way to scrape and prepare data from one of multiple sources, including the internet. It's not ideal for web testing and actually mimicking the experiences of going through a site and performing real-time, live actions on that site.
That's why this article is focusing on not only connecting to a data source, but actually interacting with it using simple codes.
Using Internet Explorer Automation in Excel VBA?
Around the same time you could first run a Web Query, Microsoft also allowed the Visual Basic programming language to access Internet Explorer WebDriver through its source file, SHDOCVW.DLL, which contains a type library for IE.
A type library contains all the information needed to create and control ActiveX objects through Automation (formerly OLE Automation). You can reference SHDOCVW.DLL in your Visual Basic projects to create and control instances of the IE application. These old code examples are all over the web.
Here's an example of using Internet Explorer with Excel VBA:
Dim ie As Object
'instantiate the IE Object
ie = CreateObject("InternetExplorer.Application")
'go to this site
ie.Navigate("http://excelvbaisfun.com/") 
Source: versionmuseum.com
Should I Use Internet Explorer with Excel?
While it's true that you can use Excel VBA commands to bring up an instance of Internet Explorer to control a website, it's also complicated, lacks features and is kind of buggy.
Not to mention, Microsoft themselves don't recommend people using Internet Explorer any longer..
In my experience, Internet Explorer is slow and buggy and doesn't play well with modern websites and things like HTML5. It's not been kept current with the times and is only kept around for legacy projects which Microsoft even recommends should be modernized (more below).
Here are some interesting articles where Microsoft says 'Do NOT use Internet Explorer'.
- Stop using Internet Explorer immediately; also, why are you still using Internet Explorer?
- The perils of using Internet Explorer as your default browser
- Microsoft security chief: IE is not a browser, so stop using it as your default
- HTML5 Limitation in Internet Explorer
A Better Method: Selenium with Excel VBA
If you like the ability to do way more than grab data from your site, but you also need to test certain capabilities in different browser environments as well as
- 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
(phew). Then you need to use Selenium with Excel...
In 2016, author and programmer Florent BREHERET created a tool called SeleniumBasic, a COM library to use Selenium with Excel in the Visual Basic Editor or within a visual basic script (VBS).
Because Selenium is such a popular tool for web scraping and website testing, the Selenium toolkit is very extensive and contains much easier methods of controlling a web browser than the old IE methods (and allows other browsers, such as Chrome, Opera and PhantomJS headless webkit).
Getting Started With Selenium and Excel
As described in Florent's GitHub page, SeleniumBasic is a Type Library ".tlb" that once installed, allows your Excel to utilize any of the associated WebDrivers. This allows you to add it to your Excel project references.
You have to first install the SeleniumBasic.exe file, then you may need to update the WebDriver of choice since newer ones might have come out since the last SeleniumBasic installer was built.
The project to put your WebDriver is typically located in the Local App Data (not Roaming App Data) folder and then in the SeleniumBasic folder. That's where to put any updated WebDrivers.
Sample Codes Using Selenium with Excel
Below are some examples of how easy it is using Selenium with Excel, such as navigating to a web page using Selenium with Excel.
Opening a ChromeDriver using Selenium with Excel (Method 1)
Dim bot As New WebDriver
bot.Start "chrome", "https://www.yahoo.com"
bot.Get "/"
Opening a ChromeDriver using Selenium with Excel (Method 2)
Dim bot As New ChromeDriver
bot.Get "https://www.yahoo.com"
Manipulating WebElements Using Selenium with Excel
Many web elements can be manipulated really easily in Selenium with Excel. There are tons of ways to access a particular element or collection of elements, but the most basic ways are by the ID attribute or the Name attribute.
Here's an example of sending text to the yahoo search bar, which has an ID of 'ybar-sbq' if you right click and inspect it.
Send Text to Yahoo Search Bar Using Selenium with Excel
'type Hello World into the search bar
bot.FindElementById("ybar-sbq").SendKeys "hello world"Pressing the Enter Key Directly in ChromeDriver Using Selenium with Excel
'press enter key
bot.SendKeys bot.Keys.Enter
The Completed Code
Sub openYahoo()
Dim bot As New ChromeDriver
bot.Get "https://www.yahoo.com"