Batch Macro - Combine different tabs from different files

Question

I'm using a directory tool to input multiple files within a folder. There are three tabs within each of these files that I need to combine. However, they are all named differently (based on the month). For example, 2019 Q1 File includes these tabs: A3a. JE 314 Feb, A3b. JE 314 Mar, A3c. JE 361. 2019 Q4 file includes these tabs: A1a Oct JEs, A1b Nov JEs and A1c Dec JEs.

I've tried a macro that pulls in the sheet names and then another macro that combines the data. However, the second macro is only pulling data in from one of the tabs and isn't including the others that I need.

Andrew_T-H · Answer

I've just had a very similar issue to this but my macro wasn't pulling through both files.

A few things to try would be to:

* change the output mode in the Properties of the interface designer in the macro to Auto Config by position or name. (I think this helps when minor schema differences occur).
* Untick the "Replace a Specific String" option in the Action tool Config. (This was my issue as the macro was only replacing the sheet names of the template file but the second file had a different name):

In your workflow, have the macro read the full file path. You will need to build this out in a formula tool placed before your macro to add the sheet names:

binu_acs · Answer

@kmcgraw2 you might need 3 batch macros for your use-case. The given workflow combines the sheets from the same file and the output will be written with the input file name "Combined.xlsx". Note: the output files are creating under the folder called "Output", so you must create the folder called output where you save the original workflow

Excel_Combine_19032025.yxzp

flying008 · Answer

Hi, @kmcgraw2

Is your second macro a standard macro or a batch macro? According to your needs, maybe the second macro that merges data needs to be set as a batch macro to achieve the results you want.