Jun 6 2012
Reading web data with VBA from excel using Microsoft HTML Object Library and Microsoft Internet Controls
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
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
Apr 22 2014
VBA: Read localstorage variable from Internet Explorer Object using temporary textfield
Here is a toy solution to access localstorage variables if they are not accessible directly.
Imagine we have test HTML webpage with HTML:
<html> <body> <script> // Put the object into storage localStorage.setItem('testObject', 'testString'); // Retrieve the object from storage var retrievedObject = localStorage.getItem('testObject'); </script> </body> </html>By Menelaos Bakopoulos • Programming, Scripts 0 • Tags: IE, LocalStorage, VBA