Automate Internet Explorer - JP Software Technologies

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

These examples use late binding with the following two object libraries: Microsoft Internet Controls (shdocvw.dll) and Microsoft HTML Object Library (MSHTML.TLB).

There are also some samples for getting data from the web using the XMLHTTP object through different web APIs on my blog.

Open a webpage and display (simple)

Sub GoToWebSite()
Dim appIE As Object ' InternetExplorer
Dim sURL As String
Application.ScreenUpdating = False

Set appIE = GetIE

sURL = "http://www.jpsoftwaretech.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 Object ' InternetExplorer.Application
Dim sURL As String
Dim UserN As Object ' MSHTML.IHTMLElement
Dim PW As Object ' MSHTML.IHTMLElement
Dim Element As Object ' HTMLButtonElement
Dim btnInput As Object ' MSHTML.HTMLInputElement
Dim ElementCol As Object ' MSHTML.IHTMLElementCollection
Dim Link As Object ' MSHTML.HTMLAnchorElement
Dim strCountBody As String
Dim lStartPos As Long
Dim lEndPos As Long
Dim TextIWant As String

Application.ScreenUpdating = False

Set appIE = GetIE

sURL = "http://www.jpsoftwaretech.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
    ' fill in first element named "username", assumed to be the login name field
    UserN(0).Value = "login name"
End If

Set PW = appIE.Document.getElementsByName("password")
' password
If Not PW Is Nothing Then
    ' fill in first element named "password", assumed to be the password field
    PW(0).Value = "my Password"
End If

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

' loop through all 'input' elements and find the one with the value "Submit"
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")

' loop through all 'input' elements and find the one with a specific value
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 = <strong>Clickable Text Link Name</strong>" Then
        Link.Click
        Exit For
    End If
Next Link

' loop until the page finishes loading
Do While appIE.Busy
  Do Events
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 &amp;amp;amp;amp; paste into Excel
appIE.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
appIE.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

Workbooks.Add
ActiveSheet.Paste

Application.ScreenUpdating = True
appIE.Quit
End Sub

I'll admit this isn't the best example; there's no login form on my homepage. But let's examine these samples closely anyway. The first one simply starts an late-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.

This function creates the InternetExplorer object:

Function GetIE() As Object
  On Error Resume Next
  Set GetIE = CreateObject("InternetExplorer.Application")
End Function

Use Do While appIE.Busy: DoEvents: 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 textbox named "username" 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 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 Latitude and Longitude functions 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.

More Examples

For even more examples, check out: