Counting Excel Sheets

Question

Hi Community,

I have an automated process where i import data from excel workbooks. Each workbook can have multiple excel sheets so i have to specify the sheet name to pick the one i want. But, is there a way of determining if an excel workbook has only 1 sheet associated with it and so import the data from that one sheet without specifying the sheet name?

grchavarri · Answer

Hi @NiallWynne 
The solution from @atcodedog05 is good if you have similar sheet schema, if not, you should use a macro as attached to read any xlsx file in a folder.

Please find attached the workflow and the macro.

I have configured in the macro, a record creator with a limit of 20 sheets but you can change it if you need so.

Hope it helps

Best,

SheetNameCommunity.yxmd

ReadSheetNames.yxmc

atcodedog05 · Answer

Hi @NiallWynne

You can do something like this.

Workflow:

1. You can configure input file path with wildcard to read all files.

2. You can configure query mode to list of sheet names.

3. You can configure to output filename.

This way you will get filename and all it sheets. Once you have made selection to can pass the specific row through dynamic input tool to read the data.

Hope this helps : )

Sheet.yxmd

NiallWynne · Answer

A sample file would really just be two random excel workbooks. One with one sheet and the other with two sheets. I have attached. It's the method of determining if the workbook has one or multiple sheets associated with it that i'm really after. Hope this helps.

One Tab.xlsx

Two Tabs.xlsx