Batch Macro for different Tabs

[etzele]

This thread is not about how to solve a particular problem but more why it is working this way:

I wanted to make a simple batch macro which is capable of combining data from different excel files with different sheet names (so input with wildcards does not work).

My initial idea was a 2-step batch macro: 1st using the Directory-Tool to get the file names, 2nd a batch macro to get the sheet names for each file and 3rd a batch macro which combines filenames and sheets to finally read out and combine the data in the files.

I was surprised that this worked with a single batch macro (see example attached to this thread) where I chose <List of Sheet Names> and replaced the complete String.

Can anybody explain me why this batch macro outputs the already unioned data sample and not the List of Sheet Names as chosen in the properties inside the Input tool of the Macro?

Looking forward to you answer(s)!

[DavidSta · Accepted solution]

Hi @etzele 

the batch macro you posted is working because of the very specific structure of the underlying Excel files.

When feeding in data to the batch macro you say "please use the full path". Within the Action Tool you define to use this full patch as the new filename instead of what is defined in the Input Data. With this you override your configuration of option 3 "Table or Query"

In reality a path would look like this: "Titanic_Samples_diff\class_1.xlsx|||`sheet_1$`" and as all of your Excel files only contain one sheet Alteryx is just using the available sheet as you have not defined in the path to use the List of Sheet Names.

If you would have multiple sheets this Macro would fail and you would have to apply your mentioned idea. So your suggested process is going to be more robust than the workflow you attached.