Understanding The Modern Design Experience Excel Pattern

Published on in Robotic Process Automation by Dan Rizea

Introduction

Are you pondering upon whether you should use the brand-new Excel Modern Experience in your own UiPath automation projects? Well look no further as I have compiled a comprehensive guide showcasing a tutorial which should provide a smooth onboarding towards understanding the whole pattern of thought necessary to get started, all the while stating the pros and cons of using this new model.

There will be a sample UiPath process containing the usage of multiple activities included at the end of the article which I recommend debugging and trying to understand.

Understanding The Key Point

The whole system is based on one key point: referencing the Excel file that is being used. All the activities that are going to follow after the “Use Excel File” activity combination must reference the Excel file as specified in the “Reference as” field.

Understanding The Modern Design Experience

As a concrete example, we can see in the figure below through a “Rename Sheet” activity that is embedded within the Modern Experience that the Excel sheet is referenced by specifying the name of the Workbook “Excel” with the additional property called “.Sheet” which has a parameter containing the name of the sheet, namely (“Sheet1”) in this case.

Example Activity

One thing to note is that the Excel referent has a wide selection of properties to choose from, those being:

“FilePath”: Returns the path of the referenced Excel file as a string.

“SelectedCell”: Returns the content of the cell that has been highlighted in the Workbook as a string.

“SelectedRange”: Returns the content of the range that has been highlighted in the Workbook. It can further be processed through specifying even more properties and it is not limited to being returned as a string that returns the address of the selected range or as a DataTable containing the values contained in the range.

“SelectedSheet”: Returns the content of the sheet that has been selected in the Workbook. It contains properties that are similar to the “SelectedRange” one.

“Sheet”: Returns the content of a sheet that can be specified as a parameter of this property. It contains the same properties that “SelectedSheet” has.

“Table”: Returns the content of a table that can be specified as a parameter of this property. It contains the same properties that “SelectedRange” has.

Parameters

Useful Modern Experience Excel Activities

There are a few activities which are mandatory and some that a developer will use more often than not. In the “mandatory” category, we have the “Single Excel Process Scope” (which can be used interchangeably with the “Excel Process Scope” activity). The main difference between the two spurs from the fact that the former can be used in order to manipulate a single Excel file while the latter can be used to process multiple Excel files.

Alongside the aforementioned activities, there is the “Use Excel File” activity which has to be nested within a “(Single) Excel Process Scope” activity. It can be considered the main component of the whole Excel automation process as it returns the Excel file as a referenceable component.

Other useful activities include, but are not limited to “Append Range”, “Copy/Paste Range”, “For Each Excel Row”, “For Each Excel Sheet”, “Read Range”, “Write DataTable to Excel”. They can all be found inside the file provided at the end of the article.

Strengths and Weaknesses

The new Modern Design Excel activities can offer the developer a very flexible and intuitively fluid mode of development as every Excel action can be easily replicated and integrated into the whole process.

It can offer features that are not necessarily available in the Classic Design experience with activities such as “For Each Excel Sheet” or “Autofit Range”, actions which can be simulated through “Invoke Code” activities, providing a substantially unpleasant robot handover experience.

The learning experience might present another upside as this new development paradigm can best be described as “hard to learn, easy to master”. Once the Key point specified above has been understood, manipulating the Excel Workbook through the new activities becomes trivial.

One major downside of using the new Modern activities is the fact that they do not allow background operations. Every activity will operate within the Excel application which will be closed only at the end of the interaction. This will have a negative impact on the performance and efficiency of the automation. However, in the rare case in which keeping the Excel application open is a development requirement, the new activities will be able to save a lot of time.

Another downside brought on by the Modern Experience is that, at the time of writing this article, some bugs are still noticeable. As an example, the “Insert Sheet” activity accepts the sheet reference if it is inserted over in the “Properties” panel, but not if it is inserted inside the activity UI itself.

Excel Bug

Lastly, in the case in which a team of developers is using the Classic Experience, transitioning towards the Modern Experience could prove to be at the very best an unpleasant endeavor as it would require time and coordination and at the very worst redundant as in its current state, the Modern Experience offers a very satisfying yet inefficient method of achieving the same goals that the Classic Experience pertains through its background manipulation capabilities.

Conclusion

The new Modern Design Experience, in the context of the new Excel activities and as specified in the previous chapter can provide a satisfying and seamless developer experience, but only at the expense of other more practical matters like efficiency through its removal of background processing.

You can find a demo which you can debug and examine by clicking this link.

Join us at Labor Automation Summit by Aggranda and fast track your digital transformation. October 4, Miami FL