Creat a Macro Batch to bring multiple sheets from multiple source files

[kauser]

Hello,

i am working in a workflow and there are multiple inputs files (around 15) and each of these file have single and/or multiple different sheets with different data. How can i create a macro that will help to bring all these file along with sheets name then i can mapped data from these sheets into different places on my workflow(as requirement). i am not combining or piling data from all these different sheets into one. i have found multiple articles and examples but all of these are compiling/piling data from all different sheets into one. For example here i illustrate list of my all input files along with different sheets name 

File Name                                                                                                                                          sheet name

ASIA	INDIA
	CHINA
EUROPE	UK
	NI
AMERICA	USA
	CA
AFRICA	EG

My expectation is how can i get all these sheets name and then also how can i map data from each one of these sheet into my workflow. 

INDIA

CHINA

UK

NI

USA

CA

EG

I also tried with Dynamic input tool without Macro then i got error message "check your path" and/or " file is corrupted" 

[alexnajm]

Seems like this was already answered and accepted by @Qiu? Solved: Re: Macro for Multiple input files with different ... - Alteryx Community

Generally the answer is to use Directory --> formula (to add "|||<List of Sheet Names>" to each full path) --> dynamic Input to read in the list of sheet names from each file --> Formula to replace the "|||<List of Sheet Names>" with the sheet column --> Batch Macro to read in all the paths (like the one found here: The Ultimate Input Data Flowchart)

[abacon]

If I understand you correctly, you want a way to feed multiple files into a macro and get the sheet names of each one so you can map them elsewhere. If that is the case, this workflow with accompanying macros should do the trick.

[kauser]

@abacon 

can you please upload the workflow again. seems like workflow was not uploaded properly. 

thank you 

[abacon]

@kauser Here you go!

[abacon]

If this worked for you @kauser, please accept it as a solution!