How to Use Object-Based Excel Automation to Accelerate Your Workflow

Worldwide, Microsoft Excel is the most popular spreadsheet software used in offices. Even with its market domination, operations also usually involve a lot of manual work with large amounts of data — copying and pasting information from Excel to other applications and vice versa, performing calculations, creating reports and pivot tables and so on — all activities that are perfect candidates for robotic process automation. So, it’s not surprising that we get a lot of questions about Excel automation in the Community Forum.

In our previous post about automating MS Excel, we covered different approaches and best practices available with RPA. Now, let’s dive deeper into one of them — object-based automation — and look at when and how to use it.

When to use an object-based Excel automation method

Object-based automation is an approach in which an RPA tool mimics user actions in a desktop application and interacts with its user interface (UI) via special UI selectors. UI selectors supported in WorkFusion Studio include Object selectors, CSS selectors, and XPaths.

This Excel automation method has several advantages when compared to using Excel actions, including:

• the ability to record actions in the file instead of building the actions flow manually
• native Excel functionality such as filtering or formatting
• keyboard shortcuts for automating commands in Excel
• actions that work with images (image-based automation), such as mouse actions or Optical Character Recognition, and Application actions
• the ability to automate password-protection of Excel files (.xlsm, .xml and others)
• the ability to observe the bot(s) work on the screen

Also, there are several scenarios in which object-based automation in Excel is more effective than using Excel actions (or in which Excel actions can’t be used at all). These can include:

Filtering and sorting data. If the process you need to automate in Excel involves sorting data in the file, applying filters or working with a filtered table, you will need to use an object-based approach. Pre-built Excel actions work with the whole array of cells you specify (or with the whole sheet, if you don’t specify an array) and disregard any filters applied to it. So, for automating an Excel table with applied filters, the bot will need to open the file on-screen and interact with the UI’s elements like a person would.

Working with charts and other objects. Excel actions in the Action Library work with strings. If you need to automate operations involving images, charts, maps, sparklines and other Excel objects, you will need to use object-based automation (probably in tandem with an image-based approach).

Applying styles and formatting. If you need to change cell styles or formatting in an automated Excel file, you’ll need to do it by automating the application’s UI.

Other cases where you should use this approach include automating file formats other than .xls and .xlsx, working with password-protected files, running macros, creating new sheets, or inserting pivot tables.

WorkFusion resources for learning object-based automation in Excel

There are several ways you can learn object-based automation in Excel.

1. Knowledge Base articles. A great place to start is WorkFusion’s Knowledge Base, which contains comprehensive articles about the actions and components you will need for automating Excel:

• RPA Recorder to capture your actions on-screen
• Mouse actions with UI selectors
• Inspector to get UI selectors

2. Automation Academy courses. The free online course “Automating the Business Task” contains a whole module devoted to object-based automation that includes videos, links to useful resources and ready examples that you can download and run with minimal customization.

3. Video tutorials. Watch our training videos to see object-based automation in practice. They are available in the Knowledge Base and on YouTube.

4. Community Forum. Don’t be shy about asking the community for assistance if you have a problem. The WorkFusion Forum contains a lot of topics about robotic process automation in Excel, and it might already have the answer to your question.

Examples of object-based Excel automation

One of the most effective ways to learn about automation is to see it in action. In the “Building Bot Tasks” course, you can find several examples of object-based automation, including two for Excel:

• Automate a password-protected Excel file: Watch as the bot opens it, inserts the password stored in the special Secrets Vault component and copies the data from the file
• How to apply filters and format an Excel table

Both examples can be downloaded from this page in the course. If you want to see how to use Excel automation in a larger workflow, you can find more complex examples in the Examples Library. You can also use components of these examples in your own scripts.