Use Excel to Automate Website Tasks

Summary

This is by far the single biggest feature added to the XLEV8 Excel add-in since it was first launched!  We all use the web quite a bit in both our professional and personal lives, right?  If you pay attention, I bet you’ll notice a lot of that usage is pretty repetitive, just like a lot of things in our lives.  What if you could automate it, well beyond basic autofill and saved passwords?  That might save you a lot of time and let you do things you’d never otherwise consider doing.

While there are plenty of ways to automate website interactions, using Excel to facilitate them adds some unique benefits:

• The steps can be saved in a template that is reusable and sharable, compounding the time savings
• The steps can be the result of formulas – references, conditions, and calculations
• The steps are quick and easy to build with copy/paste, find/replace, filtering, sorting, and referencing
• The steps can scrape data from websites, which you’ll likely want to use in Excel anyway

In this article and accompanying video, we’ll show you how to leverage the XLEV8 Excel add-in to control your web browser, including setting it up, building the steps, and sharing some example use cases.

Video Demonstration

Below is a quick demonstration of the Run Browser Steps macro in action, which can control your website interactions.

Instructions – Setup and Usage

To allow Excel to talk to your web browser, a few setup steps are required (it’s totally worth the effort!).  Here are the steps…

1. Make sure your XLEV8 add-in version is 1.3.5 or higher.  To verify your version, from the XLEV8 ribbon tab in Excel, go to “Productivity” section >> “License” menu >> About XLEV8 or alternatively Check XLEV8 Version.  You can also use the macro search box (Ctrl+Shift+W) and type either of those names.
2. Make sure you have a recent version of Google Chrome (as of the time of this article, I was on version Version 97.0.4692.71 – 64-bit, just for reference).  To check your version, click the three-dot menu icon at the top-right of your Chrome browser, then go to Settings, then go to About Chrome – the bottom option on the left.
3. Download the Selenium library (also called Selenium Basic).  This contains the library that Excel/VBA needs to talk to Chrome.
4. Make sure you have .NET Framework 3.5 or later installed.  You likely already do, but here is how to check.  To check your system’s registry, in the search box next to the Windows start button, type regedit, and open the Registry Editor app.  In the path box at the top, type the path below to see the versions of .NET you have installed.
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\
5. Update the Chrome driver file where you installed the SeleniumBasic library.  Download the relevant ChromeDriver file based on your Chrome version.  From the zipped folder you download, extract the folder and copy the ChromeDriver.exe file to the folder where installed SeleniumBasic.  The SeleniumBasic folder is usually at this path on your computer:
   C:\Users\[Your_User_Name]\AppData\Local\SeleniumBasic\

Once you follow those steps, in a blank Excel file, run the Run Browser Steps macro – either use the macro search box (Ctrl+Shift+W then type the macro name), or use the XLEV8 ribbon: “Productivity” section >> “Other” menu >> Run Browser Steps.  If it’s not quite working, there are many articles that can help, including this one. 

Building the Web Steps

When you run the Run Browser Steps macro successfully the first time, it creates the required settings sheet (named Run_Browser_Steps), much like many other bulk task macros in the add-in (see below for an example).  When this sheet is present in the active workbook and you run the macro again, it will process it, trying to perform any steps you’ve added.  Make sure not to insert any columns on this sheet or it will not work correctly.

There are a few fields in that sheet you’ll fill in that are described below.  Leverage the column header comments and cell input prompts to help you fill in the values.  (Hint: when there’s a drop-down list, press Alt+Down Arrow to show the list to pick from.)  Note that the power in doing this in Excel is that you can use formulas to quickly build the values in these fields.

• Website URL – this is self-explanatory; it’s the website address you want Chrome to interact with.  The first one (cell A2) is the most important – usually you’ll just have all the cells below reference the one above (i.e. cell A3 formula is =A2 and cell A4 formula is =A3 and so on).  Navigating from that initial page usually is done by clicking specific buttons or links.
• Action – this is what you want Excel to do for you.  Usually, it’s clicking a button/link, entering a value, or extracting a value/text.
• Value – this is the value you want Excel to enter/select, or if retrieving data, that data will be entered into this column.  It can also help with clicking finicky checkboxes (see the column header comment for those).
• Retrieve Type – this is only necessary when you are retrieving or “scraping” data (Action=Retrieve).  Note that scraping data programmatically can be a violation of website terms and conditions, so use with caution.
• Field Attribute – this is describing the method to use for selecting the web element to control.  The preferred xpath option is the most flexible and powerful, and there are several references for using it below.
• Field ID/Name/Class/Xpath – this is where you put the specific identifier for the web element you want to control.
• Pause? – this optional field allows you to specify an amount of time to pause (in seconds) after a command is run.  It’s not always necessary as Excel will wait for a page to load before attempting to control a web element, but animated interactions can sometimes cause issues, so entering 3-5 seconds here can help.  You can also enter -1 here and it will pause indefinitely (prompting you) so you can manually interact with the browser, i.e. to log in if you don’t want to store your login credentials directly within the steps in the Excel file.  Entering -1 is also useful for testing!
• Skip? – this optional field allows you to skip steps that are not always necessary.  It’s usually based on a conditional formula.  You can also use this to run just a portion of the steps instead of all of them.
• Notes – this optional field allows you to add notes so you know what each step is doing.  Notes are displayed with the pause prompt that is displayed when you enter a pause value of -1.

The screenshot below shows a few basic steps filled in on the settings sheet.

Most of the fields above are straightforward and you’ll likely do a lot of copying and pasting.  You’ll spend most of your time determining which web elements you want to control, so here are some tips and resources.  The video above also walks through some common examples.  The screenshot below is what the settings sheet will look like when it’s populated.

Take a look at the screenshot below – it’s the feedback form on the Excelevate website.  Let’s say we want to click the input item for the name.  To determine what item should be controlled (clicked, text entered, item selected, etc.), right-click the item, and select the Inspect option at the bottom.  That will show you the HTML code behind the website and the code for that item should be highlighted.  Notice it’s an <input> tag.  We’re looking for unique attributes of this element.

Using the id attribute (item[@id=’some_id’]) is a great option when they are present, but sometimes they are unique to your individual account, so they aren’t always sharable.  After some practice, you start to recognize the best way to select items, and the xpath approach works really well for this.  The second screenshot below breaks down the main components of an xpath selector.

Keys for success

• Use selectors that are specific.  If more than one item is found with your selector, the first one is used.
• Use selectors that can still be used if you share your steps with other people.  If this is your intention, test a few steps to make sure they work with other people before building something huge!
• Test along the way.  You’ll pick up things that help prevent rework down the road.
• Understanding how website pages are structured is a valuable skill – don’t give up if you get frustrated!  The answer is often a quick, specific web search away.

Resources

• https://www.w3schools.com/xml/xpath_intro.asp – great intro to what xpath is and why it’s so helpful
• https://www.scientecheasy.com/2020/07/find-xpath-chrome.html/ – article with many examples of using xpath
• https://www.scientecheasy.com/2019/08/xpath-axes.html/ – article with even more examples, such as selecting the parent, child, ancestor, sibling, etc. of other elements

While it’s easy to build the steps after a little bit of practice, if you want some help, that’s what we’re here for!  Please visit our About/Contact page and let us know what you need help with! 

Use Cases

There are endless use cases for this, but I’ll share four great ones.

Filling out an expense report

I often have the exact same expense report each month where the only thing that changes is the date.  It’s the same 30 or so clicks and data points I enter.  Surprisingly, there’s no way to save a report or transactions as templates.  I have an Excel template with all the steps to make in the browser, and most of the steps are linked to an inputs sheet with my login name and the date, as well as a list of the expenses.  After I change the inputs, I just run it, review, and submit!  Much less time is spent, and I don’t have to reference previous reports to make sure I’m entering everything correctly.  See the example template for this one – it has some creative formulas that handle the various expense types.

Entering CRM notes

My colleagues and I have to enter a lot of information in SalesForce, our CRM (customer relationship management software).  It’s the same data points for different clients.  I keep my notes in a structured Excel template.  I link those notes to the browser steps so that when I’m ready to put those notes in SalesForce, Excel does it for me!

Demo instance prep

I usually perform several software demos a week as a part of my day job at BlackLine.  Our software is web-based, and there are many prep steps I do to make sure everything looks optimal – much like when I was a user in previous roles.  Those steps take quite a bit of time, and they are extremely repetitive (30+ minutes a day!).  I listed them all out in Excel and now Excel does them for me – about 800 individual steps in about 12 minutes!

Downloading lots of reports

If you’ve ever worked in retail or restaurants in a back-office function (Ops, HR, Finance, IT, etc.), you’ll probably relate to this.  I used to download a report for each location (over 50 of them!) by filling in a few parameters, then running a report and downloading the report to Excel, then repeating for each store.  Inevitably I’d forget one or run the wrong parameters for one.  Now I list them out in Excel and I let it run them all for me.  Of course I had other macros that would open the downloaded files and copy the data so it was all in one place.