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.

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.

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.

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.

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.

The main reason why someone would choose a JSON configuration file over an Excel Workbook would be to serialize and transmit structured data over a certain network, which would provide a more secure way of transferring data. This can be useful when a user would like to send a configuration file from his local network over to one hosted on a virtual machine. Another advantage would be the fact that JSON files are very lightweight especially when compared to Excel files. In terms of user experience, Excel files are easier to modify and use by the end-user which most likely has limited experience when dealing with more exotic formats such as JSON, although the learning curve remains quite shallow.

Preparing the environment

First off, we need to create a JSON file which will encompass all of the sheets and their associated data present in the Excel file, namely “Settings”, “Constants” and “Assets”. As you can see, the nested data in the picture below strongly resembles the data used in the default configuration Excel file. Now, the developer can insert whichever additional data they need.

An additional requirement would be to install the official UiPath WebAPI activities package found in the “Manage Packages” menu in order to access the “Deserialize JSON” activity which will be used inside the Workflow that will be built in the next section of the article.

Initializing the process

Now that the data has been successfully reorganized into its new format, the developer can start working on integrating it into UiPath. The first step in this regard would be to create a new Workflow having six arguments.

• “in_JSONConfigFile” should contain the relative path of the newly created JSON configuration file.
• “inout_DTConfig” should contain the data table which will later be used to overwrite and store the key/value pairs inside the “SettingsAndConstants” argument.
• “in_ConfigSection” should contain the name of the sheet which is currently processed. (Settings, Constants, Assets)
• “in_SettingsOverwrite”, “in_ConstantsOverwrite” and “in_AssetsOverwrite” should contain boolean values pertaining to whether the existing data should be overwritten in the “inout_DTConfig” data table. These arguments are useful due to the fact that they make the component reusable.

Building the workflow

After initializing the arguments, we can start working on the workflow itself. First, we should add an “If” statement which analyzes the existence of the JSON configuration file. In the “Then” section of the statement, we should build three data tables using the “Build Data Table” activity, which will later be used to store and merge the key/value pairs from the JSON configuration file with the “inout_DTConfig” data table.

Afterwards, the JSON configuration file will have to be read through a “Read Text File” activity and deserialized through a “Deserialize JSON” WebAPI activity. The output of the deserialization should be of the “JObject” type.

Up next, we should add three “If” statements in order to check which sheet is being processed. For simplicity’s sake, I will analyze only one of them, the rest being very similar in structure, the only difference being the Sheets and data tables used as variables.

Inside the first “If” statement, we should check whether the current sheet that we are analyzing is the “Settings” one. Inside the “Then” section, we should add a “Try Catch” sequence which should contain a “For Each” statement, iterating through each key/value pair within the JSON “Settings” object. Inside the “Body” of the statement, we should declare another “If” condition verifying whether the value is empty and appending it to the previously built data table if it is not so. We should also add a catch to the “Try Catch” activity, logging an error message if the “Settings” object could not be read.

After the “Try Catch” activity is filled in, the previously populated data table should be filtered alongside “inout_DTConfig” into a new data table variable through an Inner Join query based upon the values of the keys of both joined data tables. The newly created data table must then be analyzed inside an “If” activity to see whether it has any rows and whether the “inout_DTConfig” data table must be overwritten through the “in_SettingsOverwrite” argument.

If both conditions pass off as “True”, then we shall build an error message by iterating through each row of the previously joined table and parsing the value of each key inside a string variable. Afterwards, we shall throw a system exception through the use of a “Throw” activity stating the number of duplicate names inside both the Excel and JSON files, followed by the error message containing the names of the duplicate rows. Otherwise, we should use a “Merge Data Table” activity in order to add the contents of the data table which we have populated with the JSON Configuration file contents to the “inout_DTConfig” data table argument.

Invoking the Workflow

After the reusable Workflow has been built, it must be invoked in two different places. Initially, it must be invoked after the default “Read range (Settings and Constants sheets)” activity, having the “in_SettingsOverwrite” and “in_ConstantsOverwrite” argument values set to True.

Finally, the Workflow has to be invoked after the default “Read range (Assets sheet)” activity, having the “in_AssetsOverwrite” argument value set to True with no further modifications apart from the two invocations. Additionally, it must be mentioned that the second invocation of the Workflow is optional depending on whether the Orchestrator will be used or not. Consequentially, the “in_AssetsOverwrite” argument and its pertaining activities will become redundant and therefore removable.

Conclusion

And there you have it! After following all of the steps described above, you will have a fully functional Workflow ready to load and process the contents of a JSON Configuration file.

I would like to thank my colleague, Bogdan Istrate, for building and providing the Workflow necessary in illustrating and relating the steps mentioned in my article. Here is a link to the finished project: REF_JSONDemo

Have you ever wondered whether Robotic Process Automation (RPA) is a domain that you’d like to pursue? Well, look no further than this article as I have prepared 5 reasons why RPA should more than stir up your enthusiasm. In this article, I will mainly be referring to the UiPath suite of RPA tools.

1. Great learning materials

There are all sorts of RPA courses hiding around the corners of the Internet. However, I have yet to find one that could surpass the ones offered by UiPath themselves. The UiPath Academy provides top-notch and up-to-date classes that are perfectly tailored to match various areas of expertise ranging from, but not exclusive to, business analysis and infrastructure engineering. 

In terms of course structure, UiPath has found the perfect balance between theory and practice. To be more specific, the RPA Developer Foundation and Advanced learning paths, which are required to learn the fundamentals of UiPath, consist of a carefully assembled blend of videos, text and interactive UI elements. These are topped off by the practical section which gives you the task of building an application by applying everything that you have learned. 

As a general recommendation, trying to complete the practical section by yourself is highly encouraged, no matter the amount of time that you spend on it. Taking your time to comprehend how the different elements of UiPath Studio come together will be a very good way for you to actually speed things up.

2. Reduced complexity/Intuitive IDE

An amazing feature deeply ingrained within the whole RPA paradigm is the fact that the tools which are accessible to every developer have deeply intuitive IDEs and present low- to no-code approaches, UiPath Studio is the only low-code platform available on the market that offers a more robust approach towards automating processes.

UiPath Studio can offer a developer an easy drag and drop, low-code experience which, at times, can make them feel like a one-man army of coders. It is based on the .NET framework and allows the use of either VB or C#. Prior experience with the aforementioned programming languages and IDE is not necessary, though it is strongly endorsed; in the learning phase, many features will be familiar if you’re already comfortable with one of these programming languages. 

The UiPath Studio platform also allows source control integration using GIT, TFS, FSTS, VSTS, and SVN, without having to use command lines, through only the click of one button.

Given the reduced level of complexity that UiPath Studio offers, I’d think the ideal candidate would be someone with a fairly limited background in computer science, who wants to learn and is excited about RPA. That said, developers of all kinds are more than welcome to try out everything RPA has to offer, including UiPath Studio. 

3. Industry prospects

Another amazing feature linked to RPA is the potential of the industry. Gartner classified RPA as the fastest growing corporate software category; in the next 2 years, 72% of organizations will work with RPA. According to Deloitte, in the next five years, almost all companies will have adopted RPA technology in some way, which signifies that RPA will reach universal adoption by 2023.

As companies increasingly realize the many benefits of automation, the need for RPA developers will continue to rise worldwide. While expectations may differ, a handful of best practices remain essential to the viability of the code and managing the long-term cost of automation ownership. 

4. Development satisfaction

Working with UiPath Studio can make any developer’s dopamine receptors tingle. Instead of having to labor away to write numerous methods, you can simply drag and drop them and configure them accordingly using variables, arguments and activity settings. The pleasure gained from coding can be described as similar to HTML webpage writing, where every line of code contains an immediate visual response resulting in a small dopamine incrementation.

It is also a well-known fact that working remotely in software development is a more than viable option, and it is no different in the domain of RPA. The same thing can be said about the salaries offered in the domain–RPA developers are compensated as well as standard software developers due to the pristine character of the domain and its associated demand. 

5. Potential impact on the world

Through the inherent characteristics of RPA, this technology has the potential to change everything we have known about office work and make it less cumbersome to the average employee. By freeing up the time spent by employees doing menial, repetitive tasks, we can now rest assured that more energy is directed towards tasks that actually matter and bring more value to individual companies.

The very notion that RPA is efficient only in certain industries is entirely false. Automatable tasks exist in every industry and as long as they are repetitive, rules-based and of high volume, they can be considered a great fit for RPA business processes. Furthermore, thanks to the implicitly uncomplicated nature of RPA, UiPath has developed a no-code tool called “UiPath Studio X” which allows even the most technologically illiterate employee to easily automate tasks.

In similitude with the Industrial Revolution which can be considered to be the first wave of automations in the history of mankind, RPA can definitely reach a matching level of impact as software pervades every aspect of contemporary society and will continue to expand further into more and more complex solutions which will, in turn, be automatable.

Robotic Process Automation is definitely a fast-growing domain that’s  very much accessible to both technical and non-technical persons and which, nevertheless, provides a deep level of impact upon society.