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.

VBA automation - especially directly automating a browser like IE as you'll see in this article - is exactly the sort of thing that elevates VBA from a convenient programming script into a powerful automation language. What makes it so awesome is the fact that many applications with controls or objects are created simply for the purpose of allowing you to integrate into it using the VBA programming language.

Through the years, we've showed you how to do some really cool stuff with VBA. For example, you can use it to send emails directly from inside Excel, you can automatically export Outlook tasks to an Excel spreadsheet, and you can even design your own Internet browser! It isn't just Microsoft products either. There are 3rd-party applications from all sorts of vendors that have integrated VBA and compatible objects into their software - from Adobe Acrobat SDK to the ObjectARX SDK for AutoCAD - there are ways to "plug into" more applications than you probably realize.

The Idea

In this case, you're going to connect Excel with IE. Why IE? Because Internet Explorer is so well integrated with the operating system that you really don't have to do much to start using IE automation in VBA in other Microsoft products like Word or Excel. That's the beauty of in. In this article you'll see how this automation works, and in a future article you'll see how to do nearly the same sort of thing with other  browsers.

What I'm going to show you here is a seemingly simple application, but it has plenty of applications where you could use this code to do a variety of cool things with your browser. The bottom line is that you're going to create an Excel spreadsheet for the purpose of quickly saving all of your open browser windows with one click of a button. You can save this spreadsheet and walk away or turn off your computer.

Come back an hour or three days later, open the spreadsheet, click another button and those saved URLs will reopen in the same number of tabs as you had before.  The obvious cool use of this would be to store a whole library of common online workspace setups in Excel. Then you can restore that workspace with one click of a button without having to find all of those URLs again.

Automating Internet Explorer With VBA

The first thing to do is open Excel (I'm using 2013 - other versions are similar when it comes to VBA programming) and go to the Developer menu item. Inside there, you'll see an insert button, which drops down all of your controls. Select the ActiveX pushbutton control and place it in your spreadsheet.

 

Presumably, you've already created a header for URLs if you want, but you don't have to. This is really a URL storage library, so headers don't really matter. Once you add the button, double click on it to open up the VBA editor. To the lower left, you'll see the properties for your new pushbutton.

Rename it to something like cmdSaveURLs and set the Caption to "Save URLs" - indicating that this is the button to save all open URLs from your IE browser.

Next, go to the Tools menu at the top of the VBA editor, click on References in the menu, and scroll down the long list to find the "Microsoft Internet Controls" reference. Click the checkbox to the left of it, and then click OK.

Now you're ready to roll. In the editor text area, you should see a line that reads "Private Sub cmdSaveURLs_Click()".  If you don't see it, click the left dropdown box above the text area and find cmdSaveURLs in the list. Select it, and it'll create the Click() function for you.

This is the code you want to insert into that function:

im IE As Object
    
    
            Dim shellWins As New ShellWindows
    
    
            Dim IE_TabURL As String
    
    
            Dim intRowPosition As Integer
    
    
            intRowPosition = 2
    
    
            For Each IE In shellWins
    
    
             IE_TabURL = IE.LocationURL
    
    
             If IE_TabURL <> vbNullString Then
    
    
             Sheet1.Range("A" & intRowPosition) = IE_TabURL
    
    
             intRowPosition = intRowPosition + 1
    
    
             End If
    
    
            Next
    
    
            Set shellWins = Nothing
    
    
            Set IE = Nothing

The Microsoft Scripting Runtime reference makes it so that you can access the ShellWindows object, which allows you to iterate through Windows and locate the instances of IE that you have open. This script will locate every URL you have open and write it to the Excel spreadsheet.

So, in theory if you're working on something like blogging, and you have a few items open, like research windows, your blog editor, or a calendar window  - all of those tabs will be active. If you have to shut down or leave in a hurry, it can be a real pain to save where you are by copying all those URLs.

With your new Excel script, just click the Load URLs button, and it'll load it right into the spreadsheet.

One caveat. If you aren't using a header row, then you'll want to change the line "intRowPosition=2" to "intRowPosition=1" and this will start at the first row rather than skipping the header row.

Opening Your Saved Browser Workspace

The next stage of this project is to go in the other direction. Click the "Load URLs" and have Excel launch IE and reload all of those URLs you have saved in the spreadsheet. Here's what the cmdLoadURLs_Click() function should look like.

Dim IE As Object
    
    
            Dim shellWins As New ShellWindows
    
    
            Dim IE_TabURL As String
    
    
            Dim intRowPosition As Integer
    
    
            intRowPosition = 2
    
    
            Set IE = CreateObject("InternetExplorer.Application")
    
    
            IE.Visible = True
    
    
            IE.Navigate Sheet1.Range("A" & intRowPosition)
    
    
            While IE.Busy
    
    
             DoEvents
    
    
            Wend
    
    
            intRowPosition = intRowPosition + 1
    
    
            While Sheet1.Range("A" & intRowPosition) <> vbNullString
    
    
             IE.Navigate Sheet1.Range("A" & intRowPosition), CLng(2048)
    
    
             While IE.Busy
    
    
             DoEvents
    
    
             Wend
    
    
             intRowPosition = intRowPosition + 1
    
    
            Wend
    
    
            Set IE = Nothing

There are a few steps here, but as you can see the code isn't all that long or complicated. You create a new instance of IE, make it visible (this will open IE without loading an URL). Next it'll load the first URL in the list.

The "While IE.Busy" part of the script waits until the page is fully loaded, and then move on to the rest of the URLs in your spreadsheet, opening a new tab (that's what the "CLng(2048)" does, until it hits a blank cell in your spreadsheet, then it'll stop opening new tabs. Here's my IE browser with all four original tabs recovered using the Excel IE automation script.

Summary

My real goal of doing this was to have individual spreadsheets set up collections of tabs for tasks like researching and writing on my own blog, writing on MakeUseOf, doing SEO project work on the site, or a whole list of other roles or projects that require a saved collection of tabs that are always used.

Using a spreadsheet to store those setups and automating opening them in a browser can save a lot of time...and it's actually pretty cool too.

Do you use any kind of IE automation in your VBA applications? See any other cool uses for this kind of IE control from Excel? Share your thoughts and feedback in the comments section below!