Excel is a powerful and widely used tool for data analysis, financial modeling, and business operations. But manually processing spreadsheets can be a daunting, unscalable task. By automating Excel operations, users can eliminate errors and reduce the time and effort required to complete tasks.
How MuleSoft RPA automates Excel operations
Let’s discuss how MuleSoft RPA can automate Excel operations with an example. Suppose you get a new spreadsheet with orders every day, and you need to add that information to the tracking sheet you use for the whole month. The Excel operations toolbox is ready to use in MuleSoft RPA Builder to automate this process.
Excel Session
The Excel Session is an element that encompasses all Excel operations. Excel operations include the reading, modification, and creation of entire files and file elements in the Excel format. At the beginning of the Excel Session’s execution, the Excel files are opened (and subsequently closed) upon completion of the session.
The Excel session has different operation modes including “Read Only”, “Read and write to the same file” and “Read from source and write to different file”. For the purpose of this example, we will first set up an Excel session that reads and writes to different files.
Excel Controlled Loop
You can read data from an Excel table with the Excel Controlled Loop element. The wizard provides you with numerous setting options for determining the start and the end of the iteration. You can only use the Excel Controlled Loop Action Step within an Excel session. We will now set up an excel controlled loop to read the incoming spreadsheet data.
Notice that Read mode provides the ability to define where and how to read the spreadsheet. The terminate loop option lets us define the end condition for processing. In this example, we are the entire sheet “Sheet1” and terminating when there is an empty row.
Read from Excel File
Now we can use the Read from Excel File Action Step to read the contents of an Excel file. The result will be saved as a string or an array, which can be used as an Outbound Variable in subsequent Action Steps.
The Excel mode provides the ability to read data from a Cell, Row, or Column. The Cell mode returns the data in String format whereas the other two modes return an array. Notice that we are using the pin variable for the iteration counter so that we can read all of the contents from the incoming spreadsheet until the termination condition is met.
Write to Excel
We will now use the Write to Excel file action step to write the content that we have read from an Excel file using the Read from Excel File Action Step to a destination file. The Write to Excel Action step also provides different Excel Modes to write the data, as a Cell, Row, or Column. We will choose the “Row” option and use the pin variable to map it to the iteration counter.
Next we will pin the “Array to Write” variable with the data read from the previous step.
Recalculate Excel File
The Recalculate Excel File action step can be used to recalculate an Excel sheet. It is possible to turn off automatic recalculation and recalculate open workbooks only when you explicitly do so. When this option is turned on, the Recalculate Excel File Action step lets you recalculate on demand.
With this action step, we can specify exactly where a recalculation should take place in the Workflow.
Use case in action
Now that we’ve set up the workflow, let’s look at this use case in action.
Summary
Managing Excel data can be tedious and time-consuming. With the right automation tools, it is possible to automate Excel operations to free analysts from repetitive tasks, allowing them to focus on tasks that need more manual intervention. MuleSoft RPA’s Excel Operation toolbox provides an intuitive visual interface and the ability to easily create this automation.
Loved what you learned? Discover more about how you can use MuleSoft to automate your repetitive workflows.
Want to learn more and ask questions? Visit our RPA Trailhead forum where you can engage in conversations with other RPA enthusiasts, stay up-to-date on the latest developments, and gain insights into best practices for using MuleSoft RPA.
