How to combine diff files in a folder into single file and single sheet

[anjani_priya]

How to combine diff files in a folder into single file and single sheet.
How to merge them into a single file and sheet

[bharat.c (Bharat Chavda)]

Hello @anjani_priya,

You can loop through the folders for each workbook and then use append range to append data from each workbook into a Data Table. Then can write the consolidated Data Table in a new workbook.

Regards,
Bharat

[bhavesh.choubey]

1. Use “Assign” to get all files

files = Directory.GetFiles(“C:\Your\Folder\Path”, “*.xlsx”)

Make sure to import System.IO.

2. Initialize a DataTable

Use a “Build DataTable” activity and name it dtCombined. Keep it empty initially (just define columns if you know them).

3. Loop through files

Use a For Each activity:

• TypeArgument: String
• Values: files

Inside the loop:

a. Use “Excel Application Scope”:

• Input: item

b. Use “Read Range” (inside Excel Scope):

• Output: dtTemp

c. Use “Merge DataTable”:

• Source: dtTemp
• Destination: dtCombined

Set “Preserve Changes” to True

4. Write to Final Output

After the loop ends:

• Use “Excel Application Scope” (to create/write the output file)
• Use “Write Range” to write dtCombined to a sheet

If you found this as a Solution then mark it as Solution

Happy Automation