Combine multiple excels into one with multiple tabs

[selsset97]

Hi, I am trying to combine multiple excels into one where each excel is included in a different tab.

In addition, I need each tab to be named as the name of the Excel file. 

I have +100 Excels. Is there a way to facilitate this exercise?

I have already read previous posts but for some reason I cannot re-do the code.

Can anyone integrate these excels in the following form, please?

Input (1):

 

Input (2):

 

Input (3):

Output expected:

 

Thank you. 

[ShankerV]

Hi @selsset97 

Because of the rename here, the error is prompted in the output tool as the filename is renamed at the end.

Out of 101 file, does any one has duplicate file names? 

Many thanks

Shanker V

[selsset97]

I put the same parameters you did but it still does not work.

This is the error message I have. The data I am trying to merge does not necessarily follow the same structure. Is this the reason why the error pops up? Thank you. 

[selsset97]

Hi, thank you for the response.

I have 98 files and they do not have the same structure necessarily.

I am struggling in trying to merge it with the macro.. is there any way I can get help with the macro please?

[ShankerV]

Hi @selsset97 

No, its not because the data's in multiple files are not in the same structure.

That's why I have used macro to read the files with different structure/schema.

Can you please post few screenshots of workflow, not in need to share the data's if its sensitive information.

Need to know how its modified or to investigate further.

Many thanks

Shanker V

[binuacs]

@selsset97 Make sure that your input files are having the sheet name 'Sheet1'. If not you need to create another batch macro to extract the tab names first