Count of Records - All Sheets

Question

All,

I have an excel with multiple Tabs say (40 Tabs)  and the schemas are different in each sheet and i want to produce a report just counting the record in each sheets.

Attached the sample Data with expected Output.

Is there way to achieve this using a Macro ?

Thanks

TEST.xlsx

IraWatt · Accepted Answer

Hey @suby,

Here is a batch macro which can do this:

]Any questions or issues please ask :)
HTH!
Ira

messi007 · Accepted Answer

@suby,

Please see below :

However you have to update the path inside the macro :

Attached the workflow,

Regards,

mceleavey · Accepted Answer

Hi @suby ,

I've attached a workflow which does this using repeatable tools.

The first step is to use the Directory tool in conjunction with the first bespoke tool that reads in all sheet names from the file. I then removed the Output sheet as this is not needed:

This provides the following:

At this point you join it back to the Directory tool output and create the new fullpath field including the sheet names:

At this point, given your use case, you don't need to use a batch macro to loop through each one, but you may do if you want to proceed with separate loads for each sheet. However, given your use case is to simply count the records on each sheet, you can then proceed directly to the next tool which loads the data in:

A sum tool can the group the records by sheet and provide a simple count:

I hope this helps,

M.