Combining Files with Different Schemas in Excel with Multiple Worksheets

Hi there! I'm trying to combine 10+ files currently saved in a shared folder. Unfortunately, the schema changes across the files (new fields are added or removed month after month). Each file in the folder contain 2 worksheets (1) Vacancy (2) Pivot. I'm trying to combine all the files to create a master list. I came across this YouTube clip where #3 helped in my case where there was only 1 worksheet in the file (I tested one), but results in an error when there are multiple sheets that reads "Record #: Tool #: No sheet specified, you must specify a sheet." (https://youtu.be/MjGxzsMJINQ) I tried adding a comment, but it's an old clip and doesn't seem to be getting responses. Would anyone know how to distinguish the sheet to pull in using this solution? Thanks!!

Error Message

Accepted answers

atcodedog05

Hi @Steph23

You can give this workflow also a try. Just insert the path here.

Hope this helps : )

Multiple Excel Sheets Multiple Files.yxzp

All comments

Qiu

@Steph23
I think you are looking this macro.

Multi Sheet Import Example 2.yxzp

atcodedog05

Hi @Steph23

You can give this workflow also a try. Just insert the path here.

Hope this helps : )

Multiple Excel Sheets Multiple Files.yxzp

Steph23

Hi @Qiu . I'm unable to open your attachment...

I'm using version 2018.4.4 if that helps at all...

Steph23

@Qiu unfortunately not... I'm using the version my company has issued to my computer... 😞

Qiu

@atcodedog05
You are up! 😁
Share your magic to downgrade the flow

atcodedog05

Hi @Steph23

Here is a guide on how to downgrade packaged workflows 🙂.

https://community.alteryx.com/t5/Engine-Works/Making-Workflows-Apps-amp-Macros-Backwards-Compatible/ba-p/779546

Thank you for the callout @Qiu 😀

Hope this helps : )

Steph23

@Qiu & @atcodedog05 - Thank you both so much for this information! Unfortunately, my laptop is locked down so much that I can't access the "Analytic Apps/Macros/Workflows" folder... However, I did just receive notice that our company is upgrading to the 2021 version next week, so I'll hop back in here after that happens to try again!

Steph23

I've been upgraded to 2021.2 and @atcodedog05 your solution works like a dream! (Unfortunately, I'm still unable to open your file, @Qiu 😥) Thank you so much!!

Qiu

@Steph23
Glad to know it works anyway. 😁

atcodedog05

Happy to help : ) @Steph23

Cheers and have a nice day!

Steph23

@atcodedog05 - Is there a file count limit to the macro? There are 20+ files in the folder, but the macro only seems to be pulling in 10. Any work around for this?

Steph23

sorry... hit reply too soon and it doesn't seem like we can delete messages. I realized I'm filtering on one field that's impacting how many files come through. No issues. apologies again!

lionsandrew

Trying to do something similar here

Quick Links

This months top contributors

mceleavey 382

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262