Jun 6 2012
Reading web data with VBA from excel using Microsoft HTML Object Library and Microsoft Internet Controls
Conclusions
This article aimed at providing an example of a VBA application that automates IE in order to pull information off webpages for Excel.
The explanations aim to further clarify the code.
Please provide comments and suggestions.
Reasoning for script
I was getting annoyed losing time with logging in and copying my balance information to excel.
It is not so insecure to include your password in an excel because:
- You can take various other precautions to protect the file itself (password protection, encrypted zip, truecrypt, bitlocker, or combine some etc).
- People need your actual hardware keygen to transfer funds out of your account.
- If your computer is compromised with a trojan this is the least of your worries.
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.