I’ve been tasked with downloading regular data updates from a national reporting website but as this website has user log-in functionality. This means I have to set the macro up to open the webpage and then automatically enter the user ID and password.
I found a very useful tutorial on accessing google webpages via VBA, but have expanded the detail to show excel 2007 instructions and the method for defining the correct HTMLDoc elements for any webpage. First of all you need to activate certain references from the VB Editor. Go to tools - References and then select
- Microsoft HTML Object Library
- Microsoft Internet Controls (if you can’t see this as an option try Microsoft Browser Helpers)
Dim HTMLDoc As HTMLDocument Dim oBrowser As InternetExplorer Sub Website\_Login\_Test()
Dim oHTML\_Element As IHTMLElement Dim sURL As String
On Error GoTo Err\_Clear sURL ="https://www.examplewebsite.co.uk/login.aspx" Set oBrowser = New InternetExplorer oBrowser.Silent = True oBrowser.timeout = 60 oBrowser.navigate sURL oBrowser.Visible = True
Do ' Wait till the Browser is loaded Loop Until oBrowser.readyState = READYSTATE\_COMPLETE
Set HTMLDoc = oBrowser.Document
HTMLDoc.all.loginID.Value = "exampleusername" HTMLDoc.all.Password.Value = "examplepassword"
For Each oHTML\_Element In HTMLDoc.getElementsByTagName("input") If oHTML\_Element.Type = "submit" \_Then oHTML\_Element.Click: Exit For Next
' oBrowser.Refresh ' Refresh If Needed Err\_Clear: If Err <> 0 Then Err.Clear Resume Next End If End Sub
This code works for the specific website that I am interested in but each web page could potentially have a different description for each HTML Element eg login rather than loginID.
In order to determine the correct element to use you can amend the code to print the HTML element names to the immediate window. This will then enable you to identify the obvious candidate or you could work through each until you find the one that works. Here is the amended code for identifying the appropriate HTML element from a webpage:
Sub HTML\_Element\_Printer()
On Error GoTo Err\_Clear sURL = "https://www.examplewebsite.co.uk/login.aspx" Set oBrowser = New InternetExplorer oBrowser.Silent = True oBrowser.timeout = 60 oBrowser.navigate sURL oBrowser.Visible = True
Do ' Wait till the Browser is loaded Loop Until oBrowser.readyState = READYSTATE\_COMPLETE
Set HTMLDoc = oBrowser.Document
HTMLDoc.all.loginID.Value = "exampleusername" HTMLDoc.all.Password.Value = "examplepassword"
For Each oHTML\_Element In HTMLDoc.getElementsByTagName("input") Debug.Print oHTML\_Element.Name Next
' oBrowser.Refresh ' Refresh If Needed Err\_Clear: If Err <> 0 Then Err.Clear Resume Next End If End Sub