Pinterest Stumbleupon Whatsapp
Ads by Google

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 How To Send Emails From An Excel Spreadsheet Using VBA Scripts How To Send Emails From An Excel Spreadsheet Using VBA Scripts In 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 , you can automatically export Outlook tasks to an Excel spreadsheet How To Export Your Outlook Tasks To Excel With VBA How To Export Your Outlook Tasks To Excel With VBA Whether or not you are a fan of Microsoft, one good thing that can be said about MS Office products, at least, is how easy it is to integrate each of them with one another.... Read More , and you can even design your own Internet browser How To Make Your Own Basic Internet Browser Using VBA How To Make Your Own Basic Internet Browser Using VBA When you really stop to think about it, an Internet browser in its simplest form isn't really that impressive an application. I mean, yes, the Internet is amazing by anyone's standards. The concept of linking... Read More ! 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.

Ads by Google

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.

 

IE-automation1

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.

save-urls

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.

IE-automation3

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.

IE-automation4

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.

IE-automation5

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

IE-automation6

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.

IE-automation8

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.

IE-automation9

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!

  1. RICK NEIFELD
    August 12, 2016 at 2:07 pm

    Ryan - Great article! You said "es, there's another component I'll be covering in a future article where you can do automation like this in Chrome and Firefox." MS is relegating IE in favor of Edge. Did you post an article explaining how to use Chrome and Firefox (and Edge) instead of IE? Thank you. RICK

  2. RICK NEIFELD
    August 12, 2016 at 2:04 pm

    Ryan - Did you write the article about how to do this in Chrome and Firefox (now that IE is being relegated by MS in favor of Edge)? Thank you. RICK

  3. Kasi Venkat
    July 23, 2016 at 5:20 am

    Dear Ryan,
    I did exactly like what you said.It worked wonderfully.Next Day when I started my computer--Windows-10;wih all latest updates;there was a blue screen with NetIO.Sys error.It made my system restart every time I start.The computer use to boot and when I just start my work ir gives a blue screen and restarts.I scratched my head several times-then I could arrive at the most logical solution that by mistake I had ticked in "Macro security tab" to keep the macros on.
    I deleted the whole excel book which containerd that macro and restarted.It now works beautyfully.

    I am writing this to caution all my friends over there that --keep the macros off with intimation.Otherwise you will be coming in circles for ever.

    Regards and all the best to everyone,

    K.Venkatraman
    kvinvisibleguy@gmail.com

  4. Cameron Wild
    May 4, 2015 at 10:45 am

    Thanks a lot, very helpful :) One thing though - could you help me ascertain the element name for the "Quick Add" text field and button on the Google Calendar? It simply isn't there when I follow your method of finding it (inspect element). Thanks very much.

  5. Jose Diaz
    April 20, 2015 at 3:31 pm

    Ryan,

    Interesting examples for the integration of VBA with other applications, thanks for the info.

    I'm working in a spreadsheet that needs to open an URL stored in excel, get the page code (the HTML code you can access through ctrl+u) and save it as a text for future use. Everything goes fine, but since the webpage is accessed through another webpage where I have to login, I need to force excel to open a new tab in the existing session, rather than open a new instance of IE, can you please help me in how to do that?

    Warmest regards

    Jose

    • Jose Diaz
      April 20, 2015 at 5:51 pm

      Ryan,

      Update.

      I'm able to open the webpage I want in the existing session of IE, now I need to get the HTML code of the page and save it as text, I'm using strMyPage = IE.body.innerHTML but this gives an error. Appreciate your comments

  6. qeq
    April 1, 2015 at 1:52 pm

    i want to learn full vba pls send me good link to download softwer ??

  7. qeq
    April 1, 2015 at 1:51 pm

    hey boring code bro???????????

  8. Sagar
    March 25, 2015 at 8:17 am

    Hey Ryan

    Thank you :)
    How write name in google after opening IE through VB and give me how click on search button by using code.

  9. Ravi
    March 16, 2015 at 8:42 am

    hey Ryan,
    good knowledge sharing :)
    however could you please let me know that how do i "Disable the Show Image option" using (IE object)vba code?

  10. Joseph
    March 3, 2015 at 5:49 pm

    When I try to open Internet Explorer with the "CreateObject("InternetExplorer.Application")", I get a runtime error that says "Automation Error - A system shutdown has already been scheduled". Any ideas?

  11. Suresh
    February 6, 2015 at 4:42 pm

    Hi,

    Good examples. But I have opened google using above codes. But I need to input something and click on search button..

    Is there any particular codes for that.

  12. Charmaine
    December 20, 2014 at 3:45 pm

    Hi, I tried inserting activex button but it didn't allow me to.

    What can I do to get around this?

  13. Thanks
    December 10, 2014 at 1:35 pm

    This is not Internet Explorer automation.

    This is just using Windows API to extract information from the browser.

    Internet Explorer automation involves getting Excel (or Word, or Outlook, or Access, or any MS Office program with VBA) to manipulate IE browser pages. You would use it to perform actions on the web (complete forms, click buttons, use search engines, read anchors). For instance, one could build a fairly simple workbook to visit this website and write comments in the textareas.

    Thanks for the article nonetheless. This is a useful pre-cursor to automation.

    Thanks

  14. Anonymous
    February 7, 2014 at 5:19 am

    hey Ryan, thanks for the article.. was really helpful

  15. Trev
    November 26, 2013 at 6:03 pm

    Ryan, Great article. I am wondering if there is a simple function that can access the "Back" button in the browser. Basically I need to step through several links on a single page and rather than just opening them all up at once I want to click one line, get info from it and then go back to the previous page and select the next link. Any ideas?

  16. Trev
    November 26, 2013 at 6:02 pm

    Ryan, Great article. I am wondering if there is a simple function that can access the "Back" button in the browser. Basically I need to step through several links on a single page and rather than just opening them all up at once I want to click one line, get info from it and then go back to the previous page and select the next link. Any ideas?

  17. Dan Griffin
    November 19, 2013 at 5:26 pm

    Hi Ryan, great article. Do you have any ideas with the following query.
    I have a spreadsheet that automates certain Google searches so am fine up to the point with the Google searches opening in IE. What I then want is to automate the File>"Edit with Microsoft Office Word" option so that the searches are automatically saved in Word as HTML?

  18. Mennouny
    November 11, 2013 at 8:45 pm

    hello,
    it looks great idea like it :) thanks for sharing .

    i have Just a question : is it possible to insert information to texte box (inputs) in internet page like searching " HELLO" and submet button ?

    thanks a lot

  19. Steakfask
    October 16, 2013 at 9:36 pm

    I will not "google anal " thank you very much!! :)

  20. Justin Hamilton
    October 5, 2013 at 4:59 am

    Just one more small issue: The "Load URLs" Button you reference above and in a latter explanation to a reader is actually "Save URLs" Button in your example. This is confusing to some of us newbies.

  21. Justin Hamilton
    October 5, 2013 at 4:23 am

    Thank you for sharing this. However due to the typo in the code and the fact the set of code I am supposed to paste does not match the picture of the code in your example. What am I supposed to do after I paste in the code? The instructions were very detailed up to that point.

  22. Sandi
    October 4, 2013 at 1:32 am

    this is awesome! any chance of replicating this in MS WORD?

    • Ryan Dube
      October 4, 2013 at 2:39 am

      Hi Sandi - yes, definitely. It's nearly the same code with just a few minor differences. Manipulating the IE object is exactly the same though.

    • Sandi Gauthier
      October 4, 2013 at 12:30 pm

      Thanks Ryan...as a newbie, I simply copied/pasted the Excel code into word...of course...it didn't work....would you mind providing some guidance on how to modify for Word?

  23. Baylin
    October 3, 2013 at 9:06 pm

    That's really cool!

  24. Lenny R
    October 3, 2013 at 3:37 pm

    Thank you for the article. When I setup up the spreadsheet, I had outlook open and it added the following Url.
    outlook:search%20folders/Unread%20Mail

    Do you have an idea how I can get it to ignore Outlook when running the commands?

    • Sandi
      October 4, 2013 at 1:35 am

      Yes, I was wondering that myself. You have to duplicate the steps you did to create the SaveURLs button to add a LoadURLs button. The entire VBA goes like this:

      Private Sub cmdSaveURLs_Click()

      Dim 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

      End Sub

      Private Sub cmdLoadURLs_Click()

      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

      End Sub

      hope this helps!

  25. Suvadeep P
    October 3, 2013 at 3:04 pm

    Hello Ryan,
    Nice article and helpful as well. Can it be done in Google Chrome and Firefox. Will the process remain same? Last thing is where is the "LOAD URLs BUTTON"? Cannot find that.

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

    Thanks.

    • Ryan Dube
      October 4, 2013 at 2:37 am

      Yes, there's another component I'll be covering in a future article where you can do automation like this in Chrome and Firefox. You can't use the code in this article to automate those browsers unfortunately. The Load URLs button actually refers to whatever you named the button when you created it. If you look at the picture - I named my button "Load URLs".

Leave a Reply

Your email address will not be published. Required fields are marked *