Batch Macro - Combine different tabs from different files

[kmcgraw2]

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.

[KGT]

What is happening in between your macros to construct the qualified name? This sounds like you are only feeding each file and one sheet name to the second macro. 

Without knowing what the macros are, it's hard to guess... but I assume the first one grabs all the sheet names, then you would construct the qualified names (C:\folder\thisismyfile.xlsx|||A3a. JE 314 Feb). I'm not sure how full stops go in sheet names that are not quoted so may be a little playing around with that format.

[flying008]

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.

[binuacs]

@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

[Andrew_T-H]

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: