Using VBA to Automate Internet Explorer Sessions From an Excel Spreadsheet

Manostaxx

The text that follows is owned by the site above referred.

Here is only a small part of the article, for more please follow the link

SOURCE: http://www.makeuseof.com/tag/using-vba-to-automate-internet-explorer-sessions-from-an-excel-spreadsheet/

Ryan Dube

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.How To Send Emails From An Excel Spreadsheet Using VBA Scripts How To Send Emails From An Excel Spreadsheet Using VBA ScriptsIn the past, I’ve used email a whole lot in my batch jobs and other automated scripts, just like I’ve described in past articles. These are great for those times when you have a script…READ MORE

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.

 

excelvba1

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.

excelvba2

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.

excelvba3

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.

excelvba4

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.

excelvba5

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

excelvba6

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.

excelvba8

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.

excelvba9

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s