Count of Records - All Sheets

[suby]

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

[IraWatt · Accepted solution]

Hey @suby,

Here is a batch macro which can do this:

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

[messi007 · Accepted solution]

@suby,

Please see below :

However you have to update the path inside the macro :

Attached the workflow,

Regards,

[mceleavey · Accepted solution]

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.

[suby]

Thanks All.

[IraWatt]

No worries @suby 😄