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 about 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 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:
Open your favorite web browser and navigate to the website you want to automate the logging-in procedure.
Right click on the username/password text box or on the sign-in button.
On the context menu that pops up, select the “Inspect Element” option.
At the bottom of the webpage a new window will appear containing the HTML code of the webpage.
In the highlighted line(s) of the HTML code try to find the id property.
The desired element ID will be inside the quotation marks ("").
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 webpages 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
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 webpages 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.
D. Sample macros
Finally, there are 3 sample macros that use the “WebsiteLogIn” macro in order to log-in to Gmail, Yahoo mail and Facebook. In all macros (“GmailLogIn”, “YahooMailLogIn” and “OtherLogIn”) I used the 7-step procedure that was described above in order to get the correct element IDs from the corresponding webpages.
How to use the sample workbook
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?
Downloads
The file can be opened with Excel 2007 or newer. Please enable macros before using it.