In this blog post we will talk about working with Excel files in one of the top RPA tools: UiPath. As we know Robotic process automation (RPA) is the practice of automating routine business practices with “software robots” that perform tasks automatically. These tasks include transaction processing, IT management and automated online assistants. These software robots could replace human beings for common tasks, being Excel one the most used business tools in the world.

To understand the basics of UiPath, please refer to this link.

In this example we are going to use a case defined in a practice exercise of the UiPath academy where we have different values in 2 columns (A and B) and we need to write the sum in the column C. This is a pretty simple task but we are going to do it in 3 different ways and analyze the development and execution time for each solution.

The input file example:

Output file example:

First method: Using Excel Application Scope and Get Row activities

1. This is standard way. We are going to have the following composition:
   • Main Sequence
     • Excel Application Scope
       • Read Range
       • For each row
         • Find current row
         • Get Row A
         • Get Row B
         • Assign sum to value C
         • Write value C in C Column
2. Let me explain the steps of this method:
   1. We open the Input excel file with the Excel Application Scope
   2. We read the values using the “Read Range” activity and saving the output in a Datatable variable
   3. Now for each row we get the Column A and B values with the “Get Row” activity
   4. We add the total to third variable
   5. We use the “Write Cell” activity to write the third value in the Column C

Second method: Without using Excel Application Scope

1. In this method we will do everything internally and we are going to create the C column manually. This is the composition:
   • Main Sequence
     • Read Range
     • Add data column
     • For each row
       • Assign A value
       • Assign B value
       • Set the value for Column C
       • Write Range
2. Explanation:
   1. We read the values using the “Read Range” activity and saving the output in a Datatable variable
   2. We add the Column C using “Add Data Column” activity
   3. Now for each row we get the Column A and B values using “Assign” activity
   4. We assign the sum to the Column C (row (2)) value parsing to the A and B values to integer
   5.  We write the whole table back to the original excel or a new file using “Write Range” activity

Third method: Using Excel Application Scope and Excel formulas

1. In this method we will use the Excel internal formulas to sum the values and save coding time. This is the composition:
   • Main Sequence
     • Excel Application Scope
       • Read Range
       • Assign rows count
       • Write Value
2. Explanation:
   1. We read the values using the “Read Range” activity and saving the output in a Datatable variable
   2. We find out the number of rows and assign it to a variable
   3. Now we finish by writing directly “=SUM(A1,B1)” in the “C1:C”+rowsCount range.

Conclusion:

We have successfully automate the use case using 3 different methods. Now let’s analyze the results and benefits of each case using 3 aspects:

• Development: involving the difficulty of doing it and how robust and easy to understand the code is.
• Execution time: how fast the robot executes the code
• Benefits for the business

In conclusion, the 3 methods work ok and it will depend on the business needs and developers to use the correct way for each process.

I hope you enjoy this article about UiPath and as always feel free to contact me with any questions or feedback. Thank you!