IE Automatisation with VBA


I received the task to parse an internet page to retrieve some information’s. These are the key functions of the script to be implemented:

  • Go through a list of credentials (the credential owners committed to use and check their data)
  • Login with every credential to an eLearning service url
  • Check if the login was successful
  • In case of a login error, the pop windows must be closed automatically
  • Parse the web page by accessing several frames, which are cascaded
  • Press specific buttons to access next pages
  • In the target page: retrieve the eLearning progress info and store it in a database
  • Logout, close the IE session and go for the next user.

After a short evaluation, it was immediately clear, that a VBA script should be used. The list of credentials is stored in an input sheet. The retrieved information is stored in an output sheet.

I document in this post a list of some source code examples to show how some functions have be implemented. Any specific or private data is removed to protect the real webpage.

Here the list of defined parameters, used in the examples:


Dim appIE                   As InternetExplorer
Dim sURL                    As String
Dim i                       As Integer
Dim j                       As Integer
Dim HTMLdoc                 As HTMLDocument
Dim loginFrame              As HTMLIFrame
Dim WelcomeFrameLevel1      As HTMLIFrame
Dim WelcomeFrameLevel2      As HTMLIFrame
Dim KursplanFrameLevel1     As HTMLIFrame
Dim KursplanFrameLevel2     As HTMLIFrame
Dim objBenutzerName         As HTMLInputElement
Dim objPasswort             As HTMLInputElement
Dim objLogin                As HTMLInputElement
Dim user                    As String
Dim pwd                     As String
Dim logID                   As Integer
Dim Elem                    As IHTMLElement
Dim hWND                    As Long
Dim childHWND               As Long
Dim progress                As String
Dim btZeile                 As Integer
Dim winVis                  As Boolean
Dim sheetSize               As Integer
Dim changeNr                As Integer
Dim BTname                  As String
Dim prevValue               As String
Dim actualValue             As String

Open an IE session and navigate to the target URL:


Set appIE = New InternetExplorer

' Delays the code execution to allow IE to catch up.
Application.Wait Now + TimeValue("00:00:05")
With appIE
    .Navigate sURL
    .Visible = winVis
    While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
End With
Application.Wait Now + TimeValue("00:00:05")
Set HTMLdoc = appIE.document

Remarks: you have to wait until the IE page is loaded. I used a while loop until the right status and also a wait statement.

Access a main frame:


Set loginFrame = HTMLdoc.getElementsByName("frame_name")(0)
If loginFrame Is Nothing Then
    MsgBox "Bitte Internet Verbindung prüfen"
    GoTo CancelParsing
End If
Set HTMLdoc = loginFrame.contentWindow.document

Remark: I used Firefox and the dev tool inspector to check the HTML source code of the webpage.

Access Elements


For Each Elem In HTMLdoc.all</pre>
<pre>    If Elem.tagName = "INPUT" And Elem.className = "user" Then
        Set objBenutzerName = Elem
        objBenutzerName.value = user
    End If
    If Elem.tagName = "INPUT" And Elem.className = "pwd" Then
        Set objPasswort = Elem
        objPasswort.value = pwd
   End If
   If Elem.tagName = "INPUT" And Elem.ID = "login" Then
         Exit For
   End If

Close error message box


Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hWND As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Public Const BM_CLICK = &HF5&

For i = 1 To 15
    Sleep 100
    If Not appIE.Busy Then Exit For
Next i

' catch pop up window, in case of error logins
If appIE.Busy Then
    hWND = FindWindow(vbNullString, "Message from webpage")
    If hWND <> 0 Then childHWND = FindWindowEx(hWND, ByVal 0&, "Button", "OK")
    If childHWND <> 0 Then
     Call CreateLog(logID, "Passwort falsch")
     SendMessage childHWND, BM_CLICK, 0, 0
   End If
End If

Close error message box


Set WelcomeFrameLevel1 = HTMLdoc.getElementsByName("first_frame")(0)
If WelcomeFrameLevel1 Is Nothing Then
    GoTo CancelParsing
    Set HTMLdoc = WelcomeFrameLevel1.contentWindow.document
End If
Set WelcomeFrameLevel2 = HTMLdoc.getElementsByName("second_frame")(0)
If WelcomeFrameLevel2 Is Nothing Then
    GoTo CancelParsing
    Set HTMLdoc = WelcomeFrameLevel2.contentWindow.document
End If

Remark: I used Firefox and the dev tool inspector to check the HTML source code of the webpage.

0 Kommentare

Dein Kommentar

An Diskussion beteiligen?
Hinterlasse uns Deinen Kommentar!

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.