Automate Internet Explorer
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.
To use these samples you must reference 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)
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
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 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.
