Using VBA To Automate Internet Explorer Sessions From An Excel Spreadsheet

VBA Automation of Browser From MS Excel

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 push button.

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:

Dim IE As Object
Dim shellWins As New ShellWindows
Dim IE_TabURL As String
Dim intRowPosition As Integer

intRowPosition = 3

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.

One caveat. If you aren’t using a header row, then you’ll want to change the line “intRowPosition=3″ to whatever your first row is and this will start at the first row rather than skipping the header row.