Jun 6 2012
Reading web data with VBA from excel using Microsoft HTML Object Library and Microsoft Internet Controls
Parsing the HTML
Sub getBalance()
Dim lastValue As Object
Dim last As String
If oBrowser Is Nothing Or oBrowser.document Is Nothing Then
Exit Sub
End If
For Each Item In oBrowser.document.all
If Item.getAttribute("class") = "FieldValueNormal" Then
last = Item.innerHTML
End If
Next
If ActiveCell Is Nothing Then
Exit Sub
End If
last = Replace(last, vbLf, "") 'replace linefeed
last = Replace(last, Chr(9), "") 'replace tab
last = Replace(last, " ", "") ' replace space
last = Left(last, Len(last) - 1) 'remove currency
ActiveCell.Value = (last * 1) 'set active cell
End Sub
Explanations
Note: document.all is deprecated and we should use getElementById ( http://msdn.microsoft.com/en-us/library/ie/ms537434%28v=vs.85%29.aspx )
Below we analyze various sections of the code with explanations.
Navigation and Loading
The code below displays an example of how to navigate the window to a specific URL and wait until the browser has finished loading. This is necessary so that no errors take place if we wish to click (or read) a DOM element before it has rendered.
oBrowser.navigate "http://website"
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"
Interacting with Items: Clicking and Setting
oBrowser.document.all.Item("userName").Value = "USER"
oBrowser.document.all.Item("Pswd").Value = "PASSWORD"
oBrowser.document.all.Item("selectionList").selectedIndex = 1
oBrowser.document.all.Item("login").Click
Above we have an example of 4 operations:
- Setting the textfield with id userName to “USER”
- Setting the passwordfield with id Paswd to “PASSWORD”
- Setting the selection list with id selectionList to have selected the element with index=1
- Clicking the button with id login
Conclusions and Reasoning follow 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.