MACRO - Multiple workbooks / Multiple sheets

HI All,

I have two Input files as below.

January_31-01-2023

February_28-02-2023

Where I want to take the PRODUCT sheet from each workbook and combine it and produce the Output file ( attached expected OUTPUTDATA file) but the schema is different in both the files for the PRODUCT sheet.

For example purposes I'm taking only the PRODUCT sheet but in real world there are more sheets that i want to bring in and there will be difference in schema.

Could you please provide me a Macro which can handle this situation.

Many thanks

February_28-02-2023.xlsx

January_31-01-2023.xlsx

OUTPUTDATA.xlsx

Workflow

Developer Tools

Tips and Tricks

Macros

Date Time

Common Use Cases

Batch Macro

Dynamic Processing

Database Connection

Iterative Macro

Accepted answers

binu_acs

@suby attaching the updated workflow , this workflow will pick the last 5 months data + current date's data and combine into one

CombineSheets_130423.yxzp

suby

Hi Binuacs,

Thanks for your time really appreciate it and the solution works great. Thanks again.

All comments

binu_acs

@suby The below post might be helpful for your use case, there are two batch macro attached in the post, the first macro reads all the sheet names, you can add a filter tool then filter out only the sheet you want to process

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/Read-in-Multiple-Excel-Files-with-Multiple-Tabs-that-have/ta-p/51145

suby

Hi Binuacs,

Thank you so much for the Quick Reference.

Quick clarification you mean when you say first Macro is that test.yxmc Macro ? could you please clarify where exactly to add filter and also if i want more than one sheets how to include that in that filter?

Many thanks

binu_acs

@suby yes, test.yxmc macro , add a filter after the input tool , add the condition SheetName=‘PRODUCT’, save macro, run the workflow after updating the directory tool with correct path

suby

Thanks Binuacs and when ran the workflow without any filters in the test.yxmc macro it picked up all the sheets and combined it and i got the output results as below

but can we get the output in the desired output as below with full path and the file name included as well.

Many Thanks

binu_acs

@suby Here is the updated version of the workflow attached

CombineSheets_130423.yxzp

suby

Thanks Binuacs for the solution and we have a use case to combine historical data with Daily snapshot Data the scenario as below.

December_01-12-2022 (File Generated 1st December But Data as of End of November 2022)
January_01-01-2023 (File Generated 1st January 2023 But Data as of End of December 2022)
February_01-02-2023 (File Generated 1st Feb 2023 But Data as of End of January 2023)
March_01-03-2023 (File Generated 1st March 2023 But Data as of End of February 2023)
April_01-04-2023 (File Generated 1st April 2023 But Data as of End of March 2023)
April_14-04-2023 Daily snapshot (File Generated 14th April 2023 But Data as of End of April 13th 2023)

I have the list of above Files in my local drive where I have combined all the Data using the Macro provided by you which works great thank you. Once I combine the all the history month end data with the Daily snapshot data which is fine. But I'm looking for say

when I run the workflow today

when I run the workflow tomorrow

December_01-12-2022 (File Generated 1st December But Data as of End of November 2022)
January_01-01-2023 (File Generated 1st January 2023 But Data as of End of December 2022)
February_01-02-2023 (File Generated 1st Feb 2023 But Data as of End of January 2023)
March_01-03-2023 (File Generated 1st March 2023 But Data as of End of February 2023)
April_01-04-2023 (File Generated 1st April 2023 But Data as of End of March 2023)
April_15-04-2023 Daily snapshot (File Generated 15th April 2023 But Data as April 14th 2023)