Automate Internet Explorer (IE) Using VBA

This page contains coding examples for automating Internet Explorer (IE) using VBA.

** Update 6/7/2019: Currently, the best way to achieve web automation with VBA is by using Selenium. This article DOES NOT cover Selenium. The examples below will work, and might be sufficient for your needs. However, if you have more advanced needs or want to become an expert with web automation, I strongly recommend using Selenium instead. Dan Strong’s course on Web Automation (discount available through this link) is a fantastic resource to learn Selenium:

Navigate to a Webpage with VBA

The First piece of code opens IE and navigates to a website. The second piece of code opens IE, navigates to a website and interacts with an input box.

Sub Automate_IE_Load_Page()
'This will load a webpage in IE
    Dim i As Long
    Dim URL As String
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
 
    'Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
 
    'Set IE.Visible = True to make IE visible, or False for IE to run in the background
    IE.Visible = True
 
    'Define URL
    URL = "https://www.automateexcel.com/excel/"
 
    'Navigate to URL
    IE.Navigate URL
 
    ' Statusbar let's user know website is loading
    Application.StatusBar = URL & " is loading. Please wait..."
 
    ' Wait while IE loading...
    'IE ReadyState = 4 signifies the webpage has loaded (the first loop is set to avoid inadvertently skipping over the second loop)
    Do While IE.ReadyState = 4: DoEvents: Loop   'Do While
    Do Until IE.ReadyState = 4: DoEvents: Loop   'Do Until
 
    'Webpage Loaded
    Application.StatusBar = URL & " Loaded"
    
    'Unload IE
    Set IE = Nothing
    Set objElement = Nothing
    Set objCollection = Nothing
    
End Sub

A very common problem people encounter when working with IE in VBA is VBA attempting to run code before Internet Explorer has fully loaded. By using this code, you tell VBA to repeat a loop until IE is ready (IE.ReadyState – 4).

    ' Wait while IE loading...
    'IE ReadyState = 4 signifies the webpage has loaded (the first loop is set to avoid inadvertently skipping over the second loop)
    Do While IE.ReadyState = 4: DoEvents: Loop   'Do While
    Do Until IE.ReadyState = 4: DoEvents: Loop   'Do Until

Also, note this line of code:

IE.Visible = TRUE

This code toggles whether IE runs in background or in the foreground.

Open URL and Enter Data in Form Using VBA

'This Must go at the top of your module. It's used to set IE as the active window
Public Declare Function SetForegroundWindow Lib "user32" (ByVal HWND As Long) As Long

Sub Automate_IE_Enter_Data()
'This will load a webpage in IE
    Dim i As Long
    Dim URL As String
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim HWNDSrc As Long
    
 
    'Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
 
    'Set IE.Visible = True to make IE visible, or False for IE to run in the background
    IE.Visible = True
 
    'Define URL
    URL = "https://www.automateexcel.com/vba"
 
    'Navigate to URL
    IE.Navigate URL
 
    ' Statusbar let's user know website is loading
    Application.StatusBar = URL & " is loading. Please wait..."
 
    ' Wait while IE loading...
    'IE ReadyState = 4 signifies the webpage has loaded (the first loop is set to avoid inadvertantly skipping over the second loop)
    Do While IE.ReadyState = 4: DoEvents: Loop
    Do Until IE.ReadyState = 4: DoEvents: Loop
 
    'Webpage Loaded
    Application.StatusBar = URL & " Loaded"
    
    'Get Window ID for IE so we can set it as activate window
    HWNDSrc = IE.HWND
    'Set IE as Active Window
    SetForegroundWindow HWNDSrc
    
    
    'Find & Fill Out Input Box
    n = 0
    
    For Each itm In IE.document.all
        If itm = "[object HTMLInputElement]" Then
        n = n + 1
            If n = 3 Then
                itm.Value = "orksheet"
                itm.Focus                             'Activates the Input box (makes the cursor appear)
                Application.SendKeys "{w}", True      'Simulates a 'W' keystroke. True tells VBA to wait
                                                      'until keystroke has finished before proceeding, allowing
                                                      'javascript on page to run and filter the table
                GoTo endmacro
            End If
        End If
    Next
    
    'Unload IE
endmacro:
    Set IE = Nothing
    Set objElement = Nothing
    Set objCollection = Nothing
    
End Sub

 

GetElement in IE using VBA

Interacting with objects in Internet Explorer can often be a pain. You need to identify what specific object to work with. In the above code, we are looking for the 3rd instance of “[object HTMLInputElement]” (an input form). Then we enter “orksheet” into the input form (itm.value = “orksheet”), move the cursor into the input form (itm.focus), and type “w”. Typing “w” is necessary in this instance to activate the javascript used to filter the table.

There are more direct methods to selecting objects, however this method should work if all else fails.

To use those other methods you will want to use the following options:

IE.document.getelementbyid("ID").value = "value"       'Find by ID
IE.document.getelementsbytagname("ID").value = "value"        'Find by tag
IE.document.getelementsbyclassname("ID").value = "value"      'Find by class
IE.document.getelementsbyname("ID").value = "value"           'Find by name

You can run into problems when using these methods if there are more than one element with the same name. By using a loop (as in the sample code above), you can specify which instance of the element to use.

Interact with IE using VBA

In the code above we use the event: Focus (itm.focus) to activate the cursor in the form.

You can find more examples of Object / Element Events, Methods, and Properties here: https://msdn.microsoft.com/en-us/library/ms535893(v=vs.85).aspx

Not all of these will work with every object / element and there may be quite a bit of trial and error when interacting with objects in IE.

Sendkeys to Internet Explorer

We used the Sendkeys command in the code above:

Application.SendKeys "{w}", True

Sendkeys should generally be a last resort. You should usually be able to interact with objects directly, however sometimes it’s easier to just use the Sendkeys command. Sendkeys is essentially the same as typing with the keyboard. You need to make sure the correct windows and objects are selecting before proceeding. Sendkeys can also trigger events that run based on user interaction on the web. In the example above, we use Sendkeys to activate the Javascript filter in the Table that we use on the web page.

Sendkeys has two inputs:
1. the key to enter (generally surrounded by {}… {enter}, {q}….)
2. Wait until Sendkeys has completed before proceeding TRUE/FALSE. You will generally want this set to TRUE when working with Internet Explorer.

Run Internet Explorer in Background

To run Internet Explorer in the background you need to do two things:

1. Call the macro containing the IE code with Application.Run so the macro runs in the background as you continue working:

Application.Run ("Automate_IE_Load_Page")

Note: This code could potentially interrupt your work, or your work could interfere with the code. For example, if you use SendKeys, Sendkeys may send a keystroke to the wrong application. Be very careful with this.
2. Hide IE:

IE.Visible = False

Selenium & VBA

If you found this article useful, you might want to check out Dan Strong’s course on Web Automation. It covers how to use Selenium with VBA.

Testimonial from one of Dan’s students