Automate Multiple Excel Sheet Reporting with RPA

In the field of Automation, there are five major areas where most of the automation takes place. Some of the applications are Web Applications, Email, Excel, Desktop Applications, and Citrix. Among these, Excel is a highly automated application. Excel plays a very major role in handling Structured Data.

In this blog, we'll see how to Automate Multiple Excel Sheets. Let's take an Excel file which contains three sheets Sheet1, Sheet2, and Sheet3, where Sheet3 contains the total value of each item price from Sheet1 and Sheet2(Fig.1).

Fig.1 Items and their corresponding prices in Sheet1

Here, the RPA tool we used to automate Excel is Uipath. In Uipath, there are many activities used to automate Excel.

Fig.2 Activities for Excel in Uipath

In order to automate Excel, first invoke Excel application scope in your workflow. In this activity, specify the file path of the excel file you wanted to automate. Inside this activity, invoke Read Range activity. Here we can specify our sheet name and range. This activity will read all the data in the specified sheet and range, gives the output as Datatable. We save data table in Sheet1DT.(Fig.3)

Fig.3 Invoke Excel application scope and Read Range Activity

Likewise, invoke two Read range activities to read the Sheet2, and Sheet3 data and save it into Sheet2DT and Sheet3DT

Now, we have to iterate through each row in both the Data table and sum only the Price and write Sheet3DT. Use 'While' Activity, which is used to iterate through our Datatable. Assign a variable called Counter of Integer type and have a default value as 0.

In the While loop activity, in the condition specify Counter < Sheet1DT.Columns.Count. Inside While Loop, add another assign activity and set the value as CInt(Sheet1DT.Rows(Counter)("Price").ToString). Set the value to the variable Sheet1Price. Do the same for sheet2(Fig.4).

Fig.4 While Loop to get prices of each row

Now, we have to sum up the values of both prices and assign them to a new variable called Sheet3Prcice. After adding the values write those values in Sheet3DT.

After having all the values filled in Sheet3DT, write Sheet3DT in Sheet3 Excel Sheet. So final workflow sequence will look like fig.5, fig.6, fig.7

Fig.5 Read all three excel sheets

Fig.6 Add Total Price of each row from two sheets

Fig.7 After computing Write Datatable in Sheet3

Rather than using only UiPath, the RPA tool to automate Excel sheets, we can combine it with Macro which will be much more efficient. We can write a macro and save it in .xlsm format.

In order to execute the macro you have stored, there is an activity called Execute Macro in UiPath. In this activity, all you have to do is just specify the macro name to be executed.

I hope, this blog helps you to get an overview of how to automate multiple Excel sheets using RPA.

Happy Automation.