I worked for ages on two other approaches - a batch macro that had a dynamic input to change the sheet names, and the double dynamic input suggested by @AdamR. But neither worked when I was trying to specify a range within the sheets. (I have multiple sheets with different names in multiple files - though all the sheets have the same structure.)
Yours worked like a charm!
I added a few things to the workflow to clean up the results:
- a filter to remove rows that were empty (one of the columns is null)
- a select to exclude columns that the macro captured but weren't actually part of the Excel form
- an Imputation tool to convert null number fields to zero
I also noticed that the sheet names exclusion option errored if the sheet name was longer than 6 characters. I think that is fixed if I change the field size in the select tool in that part of the workflow in the "outer" macro.
@Joe_Mako's macro includes an action tool that automatically updates the Include Subdirectories checkbox in the directory tool inside the macro. So you can use the parent directory with the macro. You can also add a filter to force the macro to only pull files where the filename is like a known pattern - in case you've got other files in those directories that you want the workflow to ignore.
but I encountered an issue that I have two sub-folders within a parent folder and in Directory I specifically only bring in the parent path. However, the macro only output one of the subfolder. When I delete the subfolder in parent folder and rerun the macro, it then recognize and output second one. Not sure why this happened, any ideas?
It's like the path for Parent is : C:\Users\Templates
and two sub-folders are C:\Users\Templates\A C:\Users\Templates\B
However, when I run the macro, it only output full path start with C:\Users\Templates\A
If I delete folder A from Templates folder and re-run exactly same macro, it then able to output C:\Users\Templates\B
I think it might be I have too many fields in excel file, cause one of the run the macro only picked up 2 of 3 files included in the same folder. If so, anyway to fix it?