Run workflow for multiple sheets and join horizontally

[elam]

Hello,

I have a built a routine that inputs different periods of data to populate a template.  I only use one input file with multiple sheets that include different 10 year periods of data. The sheets are labeled consistently (Period 1, Period 2, etc.) and follow very similar formats, but have differing field names (e.g. Period 1 includes 2022, 2023, ... 2031 but Period 2 includes 2032, 2033, ...).  As shown below, my routine performs some scrubbing procedures and uses the data to populate the standardized template, and then joins each period horizontally.  See below:

Depending on when I run this routine, I will have differing numbers of periods. Instead of manually updating the input tools and disabling containers for irrelevant periods every time, I would like to use batch macros to dynamically run the grey part of the workflow for all sheets included in a given input file, and then join these horizontally.

I've read through a million posts on batch macros, but am having trouble appending the results of the workflow.  Any advice?

[Yoshiro_Fujimori]

@elam ,

If the input sheets has different field names, I would "Transpose" the fields to standardize the metadata of output from the batch macro.

To read multiple sheets, first the the sheet names to a list and pass the file path + sheet name to the "Input Data" tool within a macro.

After all the sheets are processed, the table format can be changed using "Cross Tab" if necessary for further processing.

Does it work for you?