≡ Menu

How to Use Excel VBA to Log-In to a Website

Excel Macro web

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 activiate certain references from the VB Editor. Go to tools – References and then select

  1. Microsoft HTML Object Library
  2. Microsoft Internet Controls (if you can’t see this as an option try Microsoft Browser Helpers)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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
Doctor Moxie

Written by -

NHS Accountant with geeky tendencies - serial blogger on subjects varying from Excel, Raspberry Pi, productivity, allotment gardening and running. The NHSExcel blog is reserved for Excel topics.

Comments on this entry are closed.

  • Matt

    Brilliant, absolutly billiant.

    I have been looking for post like this for ages. I have input my user and password details, which populate. I now need to click a button called Log In, how do i do this?

    I am very very new to this, so please bear with me if the questions are not very technical.

    Thanks