Website Log-In Automation With VBA

Introduction

In the last few months, I saw a lot of people struggling to automate the log-in procedure to various websites using VBA. To be honest, web-related tasks can be considered as advanced VBA topics, since in many cases require basic knowledge of HTML language. In this post, I will try to provide some insights into how to automate the log-in procedure via VBA. More precisely, I will analyze the concept behind a reusable macro that I developed. The macro creates a new instance of Internet Explorer, navigates to the desired webpage, enters the username and password values in the corresponding text boxes of the webpage, and, finally, presses the sign-in button in order to complete the log-in procedure.

VBA code

A. Code analysis

Almost half of the code of “WebsiteLogIn” macro is used to start a new instance of Internet Explorer and navigate to the requested URL. Since late binding is used, it is necessary to use the CreateObject method and ensure that the object was created. The ShowWindow API is used to maximize the Internet Explorer window. Before the main procedure starts, the IsURLValid function is used to find if the target URL exists (see more about IsURLValid below). If yes, the procedure continues, otherwise, an error message pops up.

The other half code is dominated by the usage of the getElementById method, which returns a reference to an HTML element by using its ID. So, let’s say a few HTML things: When an HTML document is loaded into a web browser, it becomes a document object. The document object is the root node of the HTML document and the “owner” of all other nodes (element nodes, text nodes, attribute nodes, and comment nodes). The document object provides properties and methods to access all node objects (using JavaScript for example). One of the available document object methods, which is used in this example, is the getElementById method.

The getElementById method is used to find 3 elements on the target webpage: the username and password text boxes, as well as the sign-in button. This information is user-input, along with the URL of the webpage, the username and the password for logging in. But, how to find the element IDs so as to use this macro? Fortunately, with nowadays web browsers this is not a difficult task; you just have to follow the next 7 steps:

1. Open your favorite web browser and navigate to the website you want to automate the logging-in procedure.
2. Right-click on the username/password text box or on the sign-in button.
3. On the context menu that pops up, select the “Inspect Element” option.
4. At the bottom of the webpage, a new window will appear containing the HTML code of the webpage.
5. In the highlighted line(s) of the HTML code try to find the id property.
6. The desired element ID will be inside the quotation marks (“”).
7. Repeat this procedure (steps 2 to 6) for all three elements (username text box, password text box, and sign-in button).

The getElementById method was preferred in the particular case instead of other document object methods, such as getElementsByName, getElementsByTagName, and getElementsByClassName because the majority of web developers almost always assign unique ID values to the HTML elements of the web pages that they design. Returning to the “WebsiteLogIn” macro, if the getElementById finds the user-input ID of the element, it will assign a value if the element is a (username/password) text box, or it will invoke the click method if the element is a (sign-in) button. In any case, if the element ID is not found within the DOM (Document Object Model) of the webpage an error message will pop up, informing the user about the failure.

B. WebsiteLogIn macro

Option Explicit
 
'Declaring the necessary ShowWindow API function and the constant to maximize Internet Explorer window.
#If VBA7 And Win64 Then
 
    'For 64 bit Excel.
    Public Declare PtrSafe Function ShowWindow Lib "user32" _
                                    (ByVal hwnd As LongPtr, _
                                    ByVal nCmdShow As Long) As Long
 
#Else
 
    'For 32 bit Excel.
    Public Declare Function ShowWindow Lib "user32" _
                            (ByVal hwnd As Long, _
                            ByVal nCmdShow As Long) As Long
 
#End If
 
Public Const SW_MAXIMIZE = 3
 
Sub WebsiteLogIn(URL As String, UNElementID As String, UserName As String, _
                PWElementID As String, Password As String, SIElementID As String)
 
    '--------------------------------------------------------------------------------------------------------------------------
    'This macro can be used in order to log-in to a website automatically. It requires 6 parameters, which are analyzed below.
    'The macro creates a new instance of Internet Explorer, navigates to the desired site, enters the username and password
    'values in the corresponding text boxes and presses the sign-in button in order to log-in to the site.
    'The code uses late binding, so no reference to external library is required.
 
    'Required parameters:
    'URL: The website URL you want to log-in. Example: https://login.yahoo.com/?.src=ym&done=https%3a//mail.yahoo.com
    'UNElementID: The element ID of the text box, in which you write the UserName. Example: login-username
    'UserName: The username that is used for log-in. Example: MyUserName
    'PWElementID: The element ID of the text box, in which you write the Password. Example: login-passwd
    'Password: The password that is used for log-in. Example: MyPassword
    'SIElementID: The element ID of the button that you press in order to log-in. Example: login-signin
 
    'NOTE: in order to specify the values of UNElementID, PWElementID and SIElementID parameters, navigate with your browser
    'to the target page, select the username/password text boxes or the sign-in button, right click on it with the mouse and
    'press the "Inspect Element" from the pop-up menu. In the new window that will appear at the bottom of the page find in
    'the highlighted line of the HTML code the property id= and the ID of the element will be inside the quotation marks ("").
 
    'Written By:    Christos Samaras
    'Date:          18/01/2015
    'E-mail:        [email protected]
    'Site:          https://myengineeringworld.net
    '--------------------------------------------------------------------------------------------------------------------------
 
    'Declaring the necessary variables.
    Dim IE              As Object
    Dim IEPage          As Object
    Dim IEPageElement   As Object
 
    'Check if the requested URL is valid.
    If IsURLValid(URL) = False Then
        MsgBox "Sorry, the URL you provided is not valid!", vbCritical, "URL Error"
        Exit Sub
    End If
 
    'Create a new Internet Explorer instance, make it visible and maximize its window.
    On Error Resume Next
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    ShowWindow IE.hwnd, SW_MAXIMIZE
 
    'Check if the ojbect was created.
    If Err.Number <> 0 Then
        MsgBox "Sorry, it was impossible to start Internet Explorer!", vbCritical, "Internet Explorer Error"
        Exit Sub
    End If
 
    'Navigate to the requested URL.
    IE.navigate URL
 
    'Wait until the web page is fully loaded.
    Do Until IE.readyState = 4 'READYSTATE_COMPLETE in early binding
        DoEvents
    Loop
 
    'Get the document of the URL.
    Set IEPage = IE.document
 
    'Find the UserName text box using the element ID.
    Set IEPageElement = IEPage.getElementById(UNElementID)
    If Not IEPageElement Is Nothing Then
        'Pass the UserName value to the corresponding text box.
        IEPageElement.Value = UserName
        Set IEPageElement = Nothing
    Else
        'The element ID was not found, inform the user.
        MsgBox "Coould not find the '" & UNElementID & "' element ID on the page!", vbCritical, "Element ID Error"
        Exit Sub
    End If
 
    'Find the Password text box using the element ID.
    Set IEPageElement = IEPage.getElementById(PWElementID)
    If Not IEPageElement Is Nothing Then
        'Pass the Password value to the corresponding text box.
        IEPageElement.Value = Password
        Set IEPageElement = Nothing
    Else
        'The element ID was not found, inform the user.
        MsgBox "Coould not find the '" & PWElementID & "' element ID on the page!", vbCritical, "Element ID Error"
        Exit Sub
    End If
 
    'Find the Sign-In button using the element ID.
    Set IEPageElement = IEPage.getElementById(SIElementID)
    If Not IEPageElement Is Nothing Then
        'Click the Sign-In button to enter the site.
        IEPageElement.Click
    Else
        'The element ID was not found, inform the user.
        MsgBox "Coould not find the '" & SIElementID & "' element ID on the page!", vbCritical, "Element ID Error"
        Exit Sub
    End If
 
    'Release the objects.
    Set IEPageElement = Nothing
    Set IEPage = Nothing
    Set IE = Nothing
 
End Sub 

C. IsURLValid function

The code for the IsURLValid function follows. Note that this function could be also used as a built-in function. If you have various websites or individual web pages and you need to check if their URLs are valid you can use this function. In the workbook that you will find on the Downloads section, I have implemented this technique in order to add the function’s description, so as to look more like a built-in function.

Function IsURLValid(URL As String) As Boolean
 
    '--------------------------------------------------------------------
    'Checks if a URL is valid; returns True if exists and False if not.
 
    'Written By:    Christos Samaras
    'Date:          18/01/2015
    'E-mail:        [email protected]
    'Site:          https://myengineeringworld.net
    '--------------------------------------------------------------------
 
    'Declaring the necessary variables.
    Dim Request As Object
    Dim Result  As String
 
    On Error Resume Next
 
    'Create the WinHttpRequest object and check if the object was created.
    Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
    If Err.Number <> 0 Then
        MsgBox "Could not create the WinHttpRequest object!", vbCritical, "WinHttpRequest Error"
        Exit Function
    End If
 
    'Create the request using the input URL.
    Request.Open "GET", URL, False
 
    'Send the request to the server.
    Request.send
 
    'Read the request status.
    Result = Request.StatusText
 
    'Check if the request status is "OK" - the URL exists.
    If InStr(1, Result, "OK", vbTextCompare) > 0 Then IsURLValid = True
 
    'Release the WinHttpRequest object.
    Set Request = Nothing
 
End Function 

How to use the sample workbook to log in to various sites

The “Gmail” and “Yahoo” headings of the workbook I think that are self-explanatory. If you have an account to these email providers simply enter your username and password and press the corresponding log-in button. In the “Other” heading, however, the things are a little bit more complicated:

• Start by entering the URL of the website you need to log in. The IsURLValid function at the adjacent cell will return TRUE if the URL you entered exists (the cell next to the URL will become either blue or red – if the URL doesn’t exist).
• Next, following the 7-step procedure that was described in the VBA code section, find the element IDs of the username and password text boxes, as well as the ID of the sign-in button. Enter these ID values in the corresponding cells of the worksheet.
• Finally, enter your username and password and press the Other Log-In button.

With the latter procedure, you can quickly test any website you wish to log-in. In the sample workbook, for example, I have included the IDs of the Facebook log-in webpage, just to demonstrate that the same approach can be used almost in every webpage that requires log-in.

Finally, if you are wondered how the asterisks appear on the cells that contain usernames and passwords just right-click on any of them and on the context menu that will appear select Format Cells → go to the Number tab → select the Custom category → look at the format type that is applied, which is this: ;;;**. Nice trick, don’t you think?

Demonstration video

The short video demonstrates the result of the “WebsiteLogIn” macro, as well as shows the 7-step procedure that was described above.

Downloads

The file can be opened with Excel 2007 or newer. Please enable macros before using it.