Excel Automation using UiPath

Excel Automation using UiPath

What is Automation? 

Consider a scenario in which someone’s sole task is to gather data from multiple sources and then aggregate them in an Excel file. Now, do you think this person needs to do that process on a daily basis or instead automate this assignment to use his capabilities in a higher way? obviously, the second choice sounds better. So, someone can just automate this task of accumulating records from diverse assets and aggregating them right into a single document through using robotic process Automation. 

If I must define Automation for you, then Automation is the technique of reducing human effort and improve performance simultaneously. 

To automate tasks, we need RPA equipment to achieve this, and one of such tools is UiPath. With UiPath Excel Automation, manipulating Excel information will become problem and blunders-free. It saves treasured time and relieves your employees from dull, repetitive tasks and the unnecessary burden of getting to analyze significant coding. therefore, integration with different applications is carried out both smoothly and properly. UiPath Excel Automation brings you countless automation opportunities, regardless of how small or complicated the tasks you're doing. 

 Example of Excel Automation 

Task: The aim is to calculate the total marks and percentage of students automatically. 

Steps To Automate 

Follow the below steps to achieve the aim: 

• Create an excel sheet and mention the name and marks of students subject-wise. 
• Now use the Excel Application scope and mention the path and name of the workbook. 

• Now, create the sequence and use Read Range to read the data of the sheet in the workbook. 
• Use the For Each Row in the Excel Table, and use Assign activity to perform different operations such as calculating total marks and percentage. 
• Then, use the Write Range activity to automatically save data on your excel sheet. 

Solution 

Step 1: Create a variable varread of the DataTable type. We already discussed how to create a variable and change its datatype in the last article. 

 Step 2: Create an excel workbook and mention all the details. Now, open an Excel Application Scope and mention the path of the workbook in double-quotes. 

 Step 3: Now drag another Sequence and then drag a Read Range activity. In this activity mention the Sheet name of the Excel workbook and mention how much data you want to automate. Use “" if you want to automate the full sheet.  

Step 4: Now, drag the For Each Row activity and mention ForEach row in varread. 

 Step 5: Next, in the Body section, Drag the Assign activity. 

Step 5.1: Now, Write a Logic Of different operations you want to perform such as calculation of total marks and the percentage of each student. 

Step 5.2: In the Assign activity, mention CInt(row (“Full Name”)), where Full Name is the row name. 

Step 6: Now, you have to repeat the above step to calculate the percentage in the next Assign Activity. Your sequence should look like the below till now. 

Step 7: Now, after this, you have to drag an activity Write Range activity. In this, you have to mention sheet name and name of datatable variable and in properties of Write Range activity tick, the checkbox Add Headers as below. 

Step 8: Execute this sequence by clicking on the Run button. You would see that a bot automatically reads your excel sheet and perform different calculations and save it back in the excel sheet. 

Here Is how it look from start to end and the output.