Pull in Excels with Different Sheet Names

[JGHOLLAND]

I have a workflow where I use an Input Data tool where I exchange the file name for the wildcard (*).  The sheets should all be named "Sheet" but occasionally one slips in that has a different name - "default", etc.  I still want that data pulled it- the sheet name is not important for me. However, when this happens the whole workflow errors.  Is there a way to either: 

1.) Easily change all the files to have the same sheet name? Could probably write a macro to do this but I am macro illiterate... 

2.) Have Alteryx not care what the sheet name is and also use a wildcard for it as well?  

Thanks to everyone!!

[binuacs]

@JGHOLLAND use batch macro

[Manoj_k]

Hi @JGHOLLAND you can use a batch macro and then in directory you can select the entire folder. I have attached a batch macro which i use.

[JGHOLLAND]

@binuacs I'm not too familiar with this. What parts of the workflow do I need to update?  Obviously the directory location, but beyond that?  Can you shed some more light on this? 

[JGHOLLAND]

@Manoj_k   Similar to the other suggestion, how do I use this to feed my normal workflow?  I've pointed the input tool to my file and you can see it is pulling the sheet names.  One is named differently - "Kyle", as oppposed to "Sheet".  I'm not familiar with batch macro so unsure on how to use it to overcome my problem of my other workflow not wanting to read the file called "Kyle" since I told it all of the files would have sheet names called "Sheet". 

[Manoj_k · Accepted solution]

Hi @JGHOLLAND  you need to open a new workflow and bring a directory tool and set your location in directory tool and then place this macro after it it will pull the data

[binuacs]

@JGHOLLAND Update the directory path where your files located, the first macro extract all the file names and sheet names, the second macro combine all the sheets data into one