Multiple Input to One Output Excel File

[ben_d_jacob]

I have 39 different input files in one folder. and I just want them to be as 1 output excel file but different sheets..

so in short...i need 39 sheets in my output excel file... can someone please tell me the exact procedure for this?

I was facing the error - Error: Output Data (6): Unable to open file for write:
: The process cannot access the file because it is being used by another process. (32)

I tried the block until done..but it's not working for me...

is there a workaround for this?

[ben_d_jacob]

Dear @AkimasaKajitani ,

Can you please elaborate the steps. ( I did see the comments, but can you take me step by step )

Also, my input is in 39 different excel workbooks not sheets...this itself is a huge roadblock (or maybe just for me)

But, if you can, please let me know. I really appreciate the help. It'll be great if you guide me step by step on this.

[AkimasaKajitani]

Concept:

When Batch macro load the data, the schema of all files is mixed, so unnecessary field is come up at saving the data.
Therefore, at reading the data, I transpose the horizontal data into vertical and at the saving, the vertical data into horizontal.

1. Input Data tool

You can get all sheet of all Excel Files by "<List of Sheet Names>" option and using File Name "*.xlsx".
Please put the xlsx files into the same folder at this workflow.

2. Formula tool

This tool make the full path(Folder + File + SheetName) to able to read them at the next macro.

3. macro(read files)

This macro is Batch Macro.
It can read the all files that have different schema.
And it transpose all data from horizontal to vertical by Transpose tool.
This keeps the schema (field names) at the process of saving the file.

4. RegEx tool

This tool get sheetnames.

5. Text Input tool & Append Fields

This tools append the Output Excel path.

6. Formula

This tool make output full path, sheet name is changed original file name + original sheet name.

7. Select tool

Remove unnecessary fields.

8. Unique tool

This tool makes outputpath list to use grouping option at next macro.

9. macro(save files)

This macro needs "Group by" Option.
Control GroupBy Field and Input7 GroupBy Field set to the same field "OutputPath".This option is able to save at each group(file+sheet).

In this macro, Cross Tab tool transpose the vertical data to horizontal data(original form).