Manage Multiple Excel Files

The example below explains how to create a single excel report file by extracting data from multiple excel report files that have the same structure. It presents activities such as Excel Application Scope, Remove Duplicates Range, Save Workbook, Close Workbook, Read Range and Append Range. You can find these activities in the UiPath.Excel.Activities package.

This is how the automation process can be built:

1. Open Studio and create a new Process named by default Main.
2. Go to the New tab and select Sequence.
   • In the Name field write MergeReports.
   • Choose to save the process in the same location as the Main process.
   • Select Create.

note

In the project folder add the ReportTemplate.xlsx file and create a new Reports folder that includes the following .xlsx files: Report1, Report2 and Report3.

3. In the Workflow Designer, select the MergeReports tab.

4. Drag a For Each activity to the Workflow Designer.

   • In the ForEach field add the value file.
   • In the Properties panel, set the TypeArgument field to String and add ReportFiles in the Values field.

5. Drag a Sequence container in the body of the For Each activity.

   • Create a new DataTable variable named ReportTable.
   • Create a new variable named ReportTable and of type DataTable.

6. Add an Excel Application Scope activity inside the Sequence.

   • In the Properties panel, add the name Report file in the DisplayName field and the value file in the WorkbookPath field.
   • Select the check boxes for the ReadOnly and Visible options. This makes the file visible and read only to any user that accesses it without a password.

7. Drag an Excel Read Range activity and add it inside the Excel Application Scope activity.

   • In the Properties panel, add the value ReportTable in the DataTable field.
   • Select the check box for the AddHeaders option so that the first row of the table to be identified as a header.

8. Drag a new Excel Application Scope activity and place it below the Excel Application Scope activity named Report file.

   • In the Properties panel, add the name Output report in the DisplayName field and the value OutputReport in the ExistingWorkbook field.
   • Select the check box for the Visible option. This creates the file visible for any user.

9. Add an Excel Append Range activity in the body of the Excel Application Scope activity.

   • In the Properties panel, add the variable ReportTable in the DataTable field and "Sheet1" in the SheetName field.

10. Drag an Assign activity below the Excel Application Scope activity named Output report.

    • In the Properties panel, add the variable RowsCounter in the To field and RowsCounter + ReportTable.Rows.Count in the Value field.

11. In the Workflow Designer, select the Main tab.

12. Drag a Sequence container to the Workflow Designer.

    • Create the following variables:

    Variable Name	Variable Type	Default Value
    RowsCounter	Int32	N/A
    OutputReportName	String	N/A
    ReportFiles	Array of Strings	N/A
    OutputReport	WorkbookApplication	N/A

13. Add an Assign activity in the body of the Sequence.

    • In the Properties panel, add OutputReportName in the To field and String.Format("Report-{0:yyyyMMddHHmmss}.xlsx", DateTime.Now) in the Value field. This includes the current date and time in the file name.

14. Add a Copy File activity and place it below the Assign activity.

    • In the Properties panel, add the variable ReportTemplatePath in the Path field and the variable OutputReportName in the Destination field.

15. Drag a new Assign activity under the Copy File activity.

    • Add the variable ReportFiles in the To field and the syntax Directory.GetFiles(ReportsFolderPath, "*.xlsx") in the Value field.

16. Drag an Excel Application Scope after the Assign activity.

    • In the Properties panel, add the variable OutputReportName in the WorkbookPath field, add OutputReport in the Workbook field and select the check box of the Visible option for making the file visible for everyone.

17. Add a Sequence container in the body of the Excel Application Scope activity.

18. Add an Invoke Workflow File inside the newly created Sequence.

    • Set the path of the WorkflowFileName as "MergeReports.xaml".

19. Drag a Remove Duplicates Range activity and place it under the Invoke Workflow File activity.

    • In the Properties panel, add "A2:F" + (RowsCounter + 1).ToString in the Range field and "Sheet1" in the SheetName field.

20. Drag a Save Workbook activity under the Remove Duplicates Range activity.

21. Add a Close Workbook activity underneath the Excel Application Scope activity.

    • Add the variable OutputReport in the Workbook field.

22. Run the automation process. Once completed you should have a new .xlsx file that uses the predefined template and incorporates data from multiple files.

Download example