Usefulness3/5
Frequency: 4/5
Power: 2/5
Unintuitiveness: 3/5
Complexity: 4/5

MS Office: The Ribbon

The menu bar for MS Office (and some other applications) has some... peculiarities that you should know about

last changed at 2020-10-13 14:15:49 (UTC)
Intermediate
Microsoft
MS Office
MS Excel

Situation

Your robot has to push some buttons in Excel’s menu bar, but you just can’t get it to work reliably. It always works on your development machine, but when you deploy it to a robot, it fails.

The beloved Ribbon in MS Excel
The beloved Ribbon in MS Excel

Note that, while we will focus primarily on Excel here, the Ribbon behaves essentially the same way in all applications that use it, so there is no need to go into particulars. Furthermore, the examples are from Office 365, because that’s all I have available, but the behavior should be indistinguishable since Office 2013, at least.

Ribbony problems

Before we jump to solutions, let’s make sure we fully understand the many issues with the Ribbon. As is so often the case, these problems are particularly irksome for attended automation. This is because in attended scenarios, you have little to no control over the environment your robot runs in.

Collapsing

The first issue, which is relatively easy to fix, is that the Ribbon can be collapsed by clicking on the little caret button:

Collapsing the Ribbon
Collapsing the Ribbon

Which will lead to a Ribbon-less experience:

Menu bar without Ribbon
Menu bar without Ribbon

Resizing

More problematic, and usually the culprit when your MS Office automation works in development but not in production, is that the Ribbon changes its configuration upon resizing the application window. Icons are hidden dynamically under their group label.

This means the Ribbon is configured differently depending on your screen settings and window size.

As the Ribbon may also be hidden completely if the window is very small, it’s a good idea to always send aMaximize command to the target application if you decide that you absolutely have to use the Ribbon.

Ribbon configuration in a small window
Ribbon configuration in a small window

Unstable keyboard shortcuts

To add insult to injury, when the Ribbon is resized, the keyboard shortcut sequence used to trigger actions changes, too. Compare the above image to the following The shortcut sequence is also called “Access Keys”, apparently

Ribbon with full-size keyboard configuration
Ribbon with full-size keyboard configuration

Luckily, things aren’t quite as bleak as they may sound, because you can always use the full sequence of keyboard shortcuts from the smallest window configuration - even when some of the intermediate shortcuts are unnecessary. See Ribbon shortcuts for details

Customizability

This is mainly relevant for attended automation, but it can be a huge issue: users can customize their Ribbon to their heart’s content, remove items, add new items, and so on. That behavior renders the thing essentially unusable for attended automation.

A special shoutout goes to MS Word, where you can even customize global keyboard shortcuts.

Language

Another complication that afflicts attended automation is that users can and will have Office installed in different languages. This not only causes problems because you sometimes have to refer to inputs by their text, but also because, in their infinite wisdom, Microsoft decided to make your life miserable by “translating” keyboard shortcuts.

Ribbon in German with translated keyboard shortcuts
Ribbon in German with translated keyboard shortcuts
Ribbon in English
Ribbon in English

I will seize this opportunity to vent about a pet peeve of mine: in Excel, Microsoft even translated the functions used in formulas. Not in the frontend layer, either, I mean the actual formula that is used inside the engine. Apparently it does some magic when you load and save the files. There is even - I kid you not - an Excel function translator.

I don’t even… just… don’t… exit (with nervous breakdown)

Solutions

The solution, luckily, is very easy: don’t use MS Office. It sucks. I have yet to see a non-trivial task where using Python, R, a real database, LaTeX, or other sensible tools would not do a better job than Office.

So can we finally call it a day and go home now?

What? Your boss says you have to use MS Office because Joe from Accounting doesn’t know LaTeX? Sigh… ok, let’s see what we can do to help.

The solutions are ordered from best to worst. So in this pattern, I would recommend that you start at the top of the list and only go to the next proposal if the previous one is not possible for you.

Use activities instead

First and highly endorsed, UiPath comes with specialized activites for many of the MS Office products. This is true in particular for Excel, but also for Outlook, Word and, recently, PowerPoint (Excel and Outlook are part of the normal process template while the latter two reside in additional packages, see References).

There is also a stand-alone integration with Office 365 that allows you to directly edit files hosted there.

So it definitely pays to check if the problem you are trying to solve with the Ribbon cannot be solved by using some good ol’ activities instead (also see Re-engineer the process).

StudioX activities

StudioX is a somewhat simplified version of Studio and integrates with Excel heavily for everything. I think that is where the X part comes from, but the product team will neither confirm nor deny the hypothesis If you can’t find an activity that does what you need in the regular Studio activities, make sure you check StudioX, too, as the two sets of activities overlap but are not identical.

If you use a version of the UIAutomation package of 20.10 or higher, you can also enable the StudioX activities in the regular Studio by using the activities filter menu:

StudioX activities in Studio
StudioX activities in Studio

Connect Marketplace

I probably don’t have to mention this, but it sometimes helps to be reminded: maybe somebody else already solved same problem you have? The Connect Marketplace is always worth a quick search Even if the name of the platform seemingly changes back and forth every couple of months. Give it a rest, people.

Re-engineer the process

Do you tend to slavishly adhere to the process specification (PDD, video, paper napkin sketch drawn in lipstick) you got from your business users? If so, this one is for you: there are many correct ways to skin a fluffy bunny perform a business process.

Usually, your subject matter expert just shows you one way to perform the process, perhaps the one they are most used to. But there are almost always ways to do it differently, with sometimes dramatic effects in terms of simplification or time savings.During PoCs, I’ve been in numerous situations where users did steps that were wholly unnecessary, useless when you have a robot to do them (think copy&paste), or where doing them in another order allowed you to process things in a batch and avoid going back and forth between apps a bunch of times. This is not your colleague’s fault: we have to acknowledge that it is quite difficult to know how to best adapt a process to a robotic assistant. That’s where you come in!

To find these opportunities for improvement, make sure to always ask “why?” your subject matter expert prescribed a certain action, and how it could be done differently. It’s not usually enough to be able to replicate the process, you need at least a superficial understanding of the business scenario.

That is why I’m such a big fan of co-development where you sit down side-by-side with a business user and go through the process together until you understand well enough to replicate and modify it. Not to mention that this will often save a ton of time by reducing change requests to the specification from either side.

Re-engineering the Ribbon

For this particular pattern, the aim is to modify the business process in such a way that using the Ribbon becomes unnecessary: we should engineer the Ribbon away. That is, we would like to change the process flow (or the artifacts, such as files, involved) until we can forgo the Ribbon and use activities instead.

For Excel, examples include:

  • Filtering a DataTable in UiPath (or using the Filter Table activity for Excel) instead of Excel data filters
  • Turning unnamed ranges into named ranges or named tables
  • Making tables contiguous
  • Calculating things in the workflow rather than in Excel
  • Reading and writing CSV files directly so we can save ourselves the trouble of opening them in Excel for copy&paste operations (also note that Excel is terrible at handling these anyways)
  • Using conditional formatting rather than setting formatting in the robot (or vice-versa)

For Word, this could mean:

  • Using a template with formatting presets instead of applying the formatting from scratch
  • Preparing things in a DataTable rather than adding tables in-app
  • Using bookmarks creatively with the Set Bookmark Content activity (check it out, this is more useful than you might think)

Global shortcuts

Somewhat lower in order of reliability we find global keyboard shortcuts. Excel has particularly many of these, but the general idea should work for most of the Office suite. A quick web search should reveal if there is a shortcut for what you are trying to do.

Word of caution: Word allows users to customize the global keyboard shortcuts, which may render this approach unsuitable for attended automation.

Fixing the hidden Ribbon

An easy fix was promised ye, and an easy fix ye shall receive: the Ribbon’s visibility can be toggled by using the Ctrl + F1 hotkey. But ye you cannot assume the Ribbon will always be there, of course. This leads to the following solution:

Conditionally toggle Ribbon
Conditionally toggle Ribbon

This solution should be fairly self-explanatory, except perhaps for the Find Element at the beginning. Its role is to reduce the necessary timeout for Element Exists (see Find Element Exists for more information).

Reliable environment

This solution obviously only applies to unattended automation where you control the environment your robots run in. It’s conceptually pretty simple: make sure that you configure all of your machines in the same way (or at least those machines your process is supposed to run on). This is a good habit anyways, so why not put it at the top of the list?

The reason is that, in practice, strict adherence to such a high standard as “every machine is configured in exactly the same way” is much easier postulated than implemented. There are simple cases, of course, for example if the process can only run on one machine Strictly speaking, this also requires that you don’t use different machines between your test/UAT and your production environment it’s basically just a matter of making sure you test everything on that machine in addition to the development environment.

In the general, and more common, case of running your process on multiple different machines, or if you want to develop re-usable components, things are not quite as easy as that, unfortunately. While the load-out of the machine might still be standardizable when using comparable machines (with a shared image or a common installation script), keeping all software at the same version and with the exact same settings can be extremely challenging. If this is your aim, you should probably invest in Continuous Delivery (the CD part of a CI/CD pipeline) and consider an IaaS setup where you can apply versioning to your infrastructure, as well as your workflows. While I this to be the future for large-scale RPA deployments, I have only seen a handful of customers go this route yet given the complexities and investment involved.

For MS Office, this is a decent option, however, at least as long as you stick more or less to default settings. You can use Microsoft’s Customization Tool to make the installation the same for every machine. To be future-proof, I would also recommend automated activation so the user profile (configuration) is always the same - otherwise hypothetical scenarios, such as Microsoft introducing user config synchronization, might come as a nasty shock.

Setting up non-standard settings is possible with some sort of initialization script - or robot -For example for Ribbon configuration, which can be exported but once again tricky to pull off reliably in practice. Make sure you know what you’re getting into.

Macros or Interop

If you had the misfortune to learn VBA at some point along your professional journey, this is one of the rare opportunities to apply that knowledge: The next step to consider is if you can use macros to help.

Macros sometimes allow you to work with the internal objects of the MS Office suite in a less roundabout fashion than if you tried doing everything from the UiPath side, and they can be faster to boot (slower, too, though). The main issues are that macros are sometimes disabled by IT due to security concerns, and that you have to know how to write them, of course. And have a high pain threshold for a programming language that stopped being updated around the time pets.com went down the drain. Ask your parents about the “dot-com crash” if you don’t get the reference

In a similar vein, Interop, which is in some ways even more annoying than VBA, can also help expanding your options. Not for the faint of heart, but worth a mention.

Other “solutions”

The following options should only be considered as a last resort when all of the above fails. None of them is highly recommendable, especially for attended automation, but depending on the exact situation, it might be possible to get them to work.

Office 365 web app

Let me throw this in here in case you are using Office 365 and your files are stored on OneDrive or Sharepoint anyways: the Office web apps, while sharing some of the adaptability of the desktop Ribbon, are a little more reproducible in terms of selectors. It still won’t work out-of-the-box without editing the selectors, but at least buttons usually don’t vanish completely.

Ribbon in the O365 Excel web app
Ribbon in the O365 Excel web app

Context menu

The dynamic right-click menu allows you to do some of the same things the Ribbon does, but its keyboard shortcuts are more stable. If this works for what you have to do, it’s slightly better than using the Ribbon itself, but somewhat of a hassle to work with - mostly because UiExplorer doesn’t interact with it well.

File menu

If your command is available in the File menu instead of the Ribbon, try going for that instead. The selectors are more reproducible, but the layout still adapts to window size (menu items are hidden under a “More…” button), so make sure you use Pick to find the right configuration.

The Excel File menu
The Excel File menu

Ribbon shortcuts (Access Keys)

Now we’re really scraping the barrel. The Ribbon has keyboard shortcut sequences you can use to automate it. As mentioned in the problems section, they are language-dependent and change due to customization, so beware!

One interesting wrinkle in this is that the keyboard shortcut sequence changes with window size (see Resizing). The Office apps luckily ignore the superfluous group shortcuts, so taking the longest sequence is sufficient (where all groups are collapsed).

For example, to change the font to bold in Excel, you would use the following sequence: Alt, H, ZF, 1, all sent after one another as independent keystrokes. This usually works if you send the keys with a Type Into, but only with the Default type method (no background automation for you):

Sequence to set a cell style to bold
Sequence to set a cell style to bold

Adaptive clicks

Last and least, you can try to use button clicks and adjust the click sequence to what is displayed on screen, making liberal use of the Pick activity. Good luck catching all variants, though, especially when you add customization to the mix!

I would actually rank this on the same level as the Access Keys, as there are pros and cons to the two approaches (click is less susceptible to customization, but keyboard shortcuts are faster). But you probably cannot read two things in parallel, so I decided for this order.

Not recommended

Put in on the users

Given the above complications, you might be tempted to throw in the towel and release some guidance to your automation users that they have to configure their Office software in a certain way if they want to use your automation.

This is not a good idea, primarily because - as you well should know - users won’t do it (deliberately or accidentally). It’s also worth mentioning that the acceptance of such solutions is likely to plummet to such an extent that you might as well not bother offering them the automation in the first place.

Citizen Developers

A variaton on this is to decide to leverage your citizen developers and provide them with templates instead of finished automations. Which they then have to adjust to their own (or their team mates’) settings.

While slightly more reasonable, make sure you also provide them with a link to this pattern so they don’t waste a lot of time (and ask you lots of questions) trying to get the Ribbon to work on their end.

This is an option especially for the language issues in international companies, but it can come across as a bit of a cop-out if you don’t painstakingingly communicate the problems that led to choosing this approach.

References


© 2021, Stefan Reutter