Jun 6 2012

Reading web data with VBA from excel using Microsoft HTML Object Library and Microsoft Internet Controls

By • Scripts, Web • 8 • Tags: , ,

This specific content was written 13 years ago. Please keep this in mind as it may be outdated and not adhering to best-practices.
Excel VBA

Excel VBA

Description:

This is a sample script showing how I pull web-banking balances off the internet when I want to have a total picture of how my finances are. For the boring reasoning go to the last page. Otherwise technical details follow.

Project References:

Add the following to the references for this to work:

-Microsoft HTML Object Library
-Microsoft Internet Controls

VBA references

VBA references

 

Code:

The code below is an example of how to do this missing error handling. As it uses IE, it saves the developer the task of implementing SSL in VBA. 

Navigating the Site

Public oBrowser As InternetExplorer
Sub Login_2_Website()

    Dim oHTML_Element As IHTMLElement
    Dim sURL As String
    Dim text As String
    Dim avarSplit As Variant

    sURL = "https://secure.alpha.gr/e-services"
    Set oBrowser = New InternetExplorer
    oBrowser.Silent = True
    oBrowser.navigate sURL
    oBrowser.Visible = True

    Do
    ' Wait till the Browser is loaded'
    Loop Until oBrowser.readyState = READYSTATE_COMPLETE

    oBrowser.document.all.Item("_contentPlaceHolder__loadedControl_NewLayoutSignOn__userName").Value = "USER"
    oBrowser.document.all.Item("_contentPlaceHolder__loadedControl_NewLayoutSignOn__Pswd").Value = "PASSWORD"
    oBrowser.document.all.Item("_contentPlaceHolder__loadedControl_NewLayoutSignOn__login").Click

Do
    ' Wait till the Browser is loaded'
    Application.Wait DateAdd("s", 0.1, Now)
    Debug.Print (oBrowser.document.readyState)
    Debug.Print (oBrowser.LocationURL)
Loop Until oBrowser.document.readyState = "complete"

oBrowser.navigate "https://secure.alpha.gr/e-services/AWBPage.aspx?service=balancesStatements"

Do
    ' Wait till the Browser is loaded'
    Application.Wait DateAdd("s", 2, Now)
    Debug.Print (oBrowser.readyState)
    Debug.Print (oBrowser.LocationURL)
Loop Until oBrowser.document.readyState = "complete"

Application.Wait DateAdd("s", 2, Now)

oBrowser.document.all.Item("_contentPlaceHolder__loadedControl_balancesStatements__productsPagedDropDownList__selectionList").selectedIndex = 1
oBrowser.document.all.Item("_contentPlaceHolder__loadedControl_balancesStatements__productsPagedDropDownList__selectedIndexHiddenField").Value = "1"
oBrowser.document.all.Item("_contentPlaceHolder__loadedControl_balancesStatements__balancesButton").Click

Do
' Wait till the Browser is loaded'
    Application.Wait DateAdd("s", 0.1, Now)
    Debug.Print (oBrowser.document.readyState)
    Debug.Print (oBrowser.LocationURL)
Loop Until oBrowser.document.readyState = "complete"

getBalance

End Sub

 

 

Analysis follows on next page More