Automating Internet Explorer

    Here I will demonstrate some example code for automating Internet Explorer through Excel. You can open web pages, click buttons, import data, etc. The possibilities are endless.

    To use these samples you must reference two object libraries: Microsoft Internet Controls (shdocvw.dll) and Microsoft HTML Object Library (MSHTML.TLB).

Open a webpage and display (simple)

Sub GoToWebSite()

Dim appIE As InternetExplorer
Dim sURL As String
Application.ScreenUpdating = False

Set appIE = New InternetExplorer

sURL = "http://www.codeforexcelandoutlook.com"

With appIE
    .Navigate sURL
    .Visible = True
End With

Application.ScreenUpdating = True

Set appIE = Nothing
End Sub

Open a webpage, fill in form fields and click buttons

Sub GoToWebSiteAndPlayAround()

Dim appIE As InternetExplorer
Dim sURL As String
Dim UserN As Variant, PW As Variant
Dim Element As HTMLButtonElement
Dim btnInput As MSHTML.HTMLInputElement
Dim ElementCol As MSHTML.IHTMLElementCollection
Dim Link As MSHTML.HTMLAnchorElement
Dim strCountBody As String
Dim lStartPos As Long
Dim lEndPos As Long
Dim TextIWant As String

Application.ScreenUpdating = False

Set appIE = New InternetExplorer

sURL = "http://www.codeforexcelandoutlook.com"

With appIE
    .Navigate sURL
    ' uncomment the line below if you want to watch the code execute, or for debugging
    '.Visible = True
End With

' loop until the page finishes loading
Do While appIE.Busy
Loop

' enter username and password in textboxes
Set UserN = appIE.Document.getElementsByName("username")
If Not UserN Is Nothing Then
    UserN(0).Value = "login name"
End If

Set PW = appIE.Document.getElementsByName("password")
' password
If Not PW Is Nothing Then
    PW(0).Value = "my Password"
End If

' click 'Submit' button
Set ElementCol = appIE.Document.getElementsByTagName("INPUT")

For Each btnInput In ElementCol
    If btnInput.Value = "Submit" Then
        btnInput.Click
        Exit For
    End If
Next btnInput

' loop until the page finishes loading
Do While appIE.Busy
Loop

' click a button on the next page
Set ElementCol = appIE.Document.getElementsByTagName("INPUT")

For Each btnInput In ElementCol
    If btnInput.Value = "Link Page #1" Then
        btnInput.Click
        Exit For
    End If
Next btnInput

' loop until the page finishes loading
Do While appIE.Busy
Loop

' click a text link on the page after that
Set ElementCol = appIE.Document.getElementsByTagName("a")

For Each Link In ElementCol
    If Link.innerHTML = "<B>Clickable Text Link Name</B>" Then
        Link.Click
        Exit For
    End If
Next Link

' loop until the page finishes loading
Do While appIE.Busy
Loop

' grab some text from the body
strCountBody = appIE.Document.body.innerText
lStartPos = InStr(1, strCountBody, "Text to find")
lEndPos = lStartPos + 12
TextIWant = Mid$(strCountBody, lStartPos, lEndPos - lStartPos)

' grab the whole screen & paste into Excel
appIE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
appIE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

Workbooks.Add
ActiveSheet.Paste

' destroy variables and end
Application.ScreenUpdating = True
Set UserN = Nothing
Set PW = Nothing
Set ElementCol = Nothing
Set appIE = Nothing
End Sub

    Let's examine these samples closely. The first one simply starts an early-bound instance of Internet Explorer, assigns a url (string) to a variable and then passes it to the Internet Explorer application variable. Then it makes the window visible, destroys the variable and exits.

    The second example shows in much more detail how to manipulate Internet Explorer.

Use Do While appIE.Busy: Loop to keep Excel waiting until the page loads completely.

Textboxes (username, password, etc) that you fill in are usually named elements on a webpage. The code uses:

appIE.Document.getElementsByName("username")

to find the named textbox and put your chosen text there. To find the name of the text box, click on View>Source in Internet Explorer when viewing the page. You will be using View>Source extensively to get the names of various elements on each page you want to access.

    To click buttons on a site, you can cycle through each Input Element of the Elements Collection until you find the button named "Submit":

appIE.Document.getElementsByTagName("INPUT")

    Again you would need to View>Source to see the tag name is "INPUT" (the type of element) and the name of the button is "Submit" The Click Method actually does the clicking of the button to load the next page or input whatever you placed in the text boxes.

    To click on a link on a page, you would loop through the tags that start with 'a' (links) until you find the one with the exact name you are looking for. You need to include everything between the <a> and </a> tags; so for example in the code above, the webmaster put 'bold' tags in the link (). That would need to be included because technically it is part of the text of the link (but bad web design if you ask me).

appIE.Document.getElementsByTagName("a")

    Now to grab the body text (the real reason you are here, I'm sure), assign the innerText property to a String variable:

appIE.Document.body.innerText

    In the sample above, I use the Instr property to look for some text, then the Mid function to extract it from the webpage. At that point you could simply paste it into a workbook (see Excel VBA Samples for the GetDistance() UDF) but I went on and just copied and pasted the entire displayed webpage into a new workbook.

Hopefully these samples will help you automate Internet Explorer in your own projects.

LAST UPDATED: February 5, 2008