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
HTML Object Libray: Essentially Creating a Script to Pull Personal Finances From Online Bank Account
August 25, 2012 @ 11:08 pm
[…] three sites I'm using as references for my code: VBA Express : Excel – Log In to an Internet Site Reading web data with VBA from excel using Microsoft HTML Object Library and Microsoft Internet Cont… InternetExplorer Object Here's the code I have so far: […]
December 3, 2012 @ 9:41 pm
I’m trying a similar approach to get data from our data entry site. So far, I’m able to log in, click on the submit button and go to the next page. After that, my debugger keeps showing only the links from the original page to which I navigated. I’d welcome any suggestions:
Sub Login_2_Website(user As String, pw As String, myUrl As String) Dim oHTML_Element As IHTMLElement Dim sURL As String Dim newURL As String On Error GoTo Err_Clear Set oBrowser = New InternetExplorer oBrowser.Silent = True oBrowser.timeout = 60 oBrowser.navigate myUrl oBrowser.Visible = True Do ' Wait till the Browser is loaded Loop Until oBrowser.readyState = 4 Set HTMLDoc = oBrowser.document HTMLDoc.all.user.Value = user HTMLDoc.all.pswd2.Value = pw 'For Each oHTML_Element In HTMLDoc.all 'Debug.Print oHTML_Element.getAttribute("ID") 'Next prevAddress = HTMLDoc.URL For Each oHTML_Element In HTMLDoc.getElementsByTagName("button") If oHTML_Element.TYPE = "submit" Then oHTML_Element.Click End If Next 'oBrowser.Refresh ' Refresh If Needed 'newURL = prevAddress & "/misk5/formz/collections" 'Call GetInternetExplorer(newURL) For Each link In HTMLDoc.Links '********* here's where I get stuck '********* Suggestions welcome 'If oHTML_Element.innerHTML = "/misk5/formz/collections" Then ' Link.Click 'Exit For 'End If Debug.Print link Next Err_Clear: If Err 0 Then 'Debug.Assert Err = 0 Err.Clear Resume Next End If End SubJanuary 29, 2013 @ 11:39 pm
Hi, apologies for late reply. First off I notice your in a loop which never ends unless an error takes place. So, even if your codes executes up to Debug.Print and Next, you end up trying to re-login at the beginning of your loop.
Except for that though, I think you need to add the following after the Click command,
Do ' Wait till the Browser is loaded Application.Wait DateAdd("s", 2, Now) Loop Until HTMLDoc.readyState = "complete"This will cause your script to wait until the next page has completed loading, and hence the links will be available.
December 14, 2013 @ 8:44 pm
I want to extract some data for internet and put it into excel. I am new to VB. Kindly suggest some books or website which can help
December 29, 2013 @ 12:09 pm
Is there a way to get those “username” and “password” names from table or form. I mean not the values of “username” and “password” but the names of those fields in HTML.
What I want to do is to use one universal script to log to many different sites. The only difference will be the names of “username” and “password” fields in HTML of the site. So I want to write something like this:
HTMLDoc.all.FORM!CONTROL.Value = ” “
February 11, 2014 @ 4:24 am
This is very possible. It seems like you want to make a password excel file for easy login to multiple sites?
First off, I would use a dictionary (Array/List) of different IDs that could represent a password field.
Following, I would do a simple search to find a field that matches this, or use an algorithm such as levenstein distance (see http://stackoverflow.com/questions/4243036/levenshtein-distance-in-excel ), to find a field that is a very close match.
How to actually find the control?
You could try something as inefficient as below, or use a more optimized approach.
For Each Item In oBrowser.document.all If Item.getAttribute("id") = "passwd" Then last = Item.innerHTML End If NextApril 22, 2014 @ 8:30 am
Hi, Is there a script in vba that will read localStorage.setItem(‘key’, ‘xx’ ); memory which is stored in the computer when connected via web browers and is stored by the web brower.
many thanks dee
April 22, 2014 @ 7:56 pm
Since your problem was interesting I tackled it and came up with: http://maythesource.com/2014/04/22/vba-read-localstorage-variable-from-internet-explorer-object-using-temporary-textfield/ . Ofcourse it can be improved with additional error handling and to use something more random than test1234 for the temporary textfield (as well as making it hidden).
Additionally, monitor http://stackoverflow.com/questions/23226837/vba-access-localstorage-from-internetexplorer-object in case something more efficient exists.