Filter to Identify Excel Files with Multiple Worksheets

[Incis]

Hi,

I created a batch macro to read all data in one folder. My batch macro only reads files with single worksheet (I also intended to read only files with one worksheet). I would like the batch macro to read through all files and tell me which files have multiple worksheets, so that I can go to them directly and delete additional worksheets. I think a combination of "filter" tool and "message" tool would do the job, but I did not figure out how yet. Maybe somebody can give me some ideas. I attached my current batch macro. Many thanks!

 

[AmeliaG · Accepted solution]

Hi @Incis,

Thanks for your question! I've attached a batch macro which takes in a file path and counts the number of sheets in each Excel file. This will allow you to filter the files with more than one sheet. 

Let me know if you have any additional questions and I'd be happy to help!

Amelia

[Incis]

Hi @AmeliaG,

Thank you for your help! It works perfectly for xlsx files.

But xls does not have <List of Sheet Names> option in the Input tool. Is there any workaround?

Best Regards,

Ken

[AmeliaG]

@Incis

Unfortunately, no - I do not know of any work around for xls files. The Sheet names option is not available. Is is possible to update your files to xlsx?

Please note, recent updates issued by Microsoft have impacted the way xls files are read into Alteryx. Since you have not experienced this issue, it is likely you have not installed the update. But just in case you see any issues in future, please familiarize yourself with how to address it here.

Hope you have a great day!

Amelia

[Incis]

Ok. Is there any way to update xls files with multiple sheets to xlsx files by using a batch marco? Or should I rather open up another question thread?

Hope you will have a great day too!

Ken