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.
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).