Consolidated multiple excel files into one excel

Help StudioX
1

Hello,

I am having trouble trying to do the following: we have a folder with say 20 Excel files (in .xls format) and all of them have the same column structure and same sheet name (sheet name is called “Report” for example).

What I want to do is loop through each of the .xls files in the folder, copy the contents from the Sheet Name “Report”, and then paste the contents into another Excel file (lets call this Output.xlsx). So the idea being the contents of each of the .xls files will be appended on top of each other, in the Output.xlsx file.

Hope that makes sense, appreciate any help on this!

2

Read range and append range activities can help you.

Follow these steps:

  1. Get the list .xls files in your folder
  2. Loop through those files. For each file, follow the below steps:
    Read the excel file data into a data table variable(say dt1), use Read range activity(this is under workbook section in activities panel)
    Append the data from dt1 into Output.xlsx using write range activity

Hope this helps you.

2 Likes
3

Read range every excel file and then write range in the same excel but in a different sheet. Use the individual name of each excel as sheet name in the common output excel.