Best way to manage an optional first tab in Excel

1

I need to create a first sheet in Excel if a client has more than one “Master Total”, but if they only have one “Master Total” I don’t want to create this sheet.

I can get around this easily enough by creating the sheet by default, and when all the reports are created, going back and deleting the sheet by name if the client only has one “Master Total”.

The Excel spreadsheet sheets will look like this initially with the first sheet the Excel file creation point:

1 Master Totals
2 Products 1 Totals
3 Products 1 breakdown
4 Products 1 Totals
5 Products 2 breakdown

If a client only has one list of Products, I skip the creation of any empty tables after the file is created with the Master Totals sheet, and then delete the Master Totals sheet to give for that client:

1 Products 1 Totals
2 Products 1 breakdown

Is there a more elegant way of doing this? It takes about 4 seconds per report to delete that one sheet and as I have 236 reports being generated at the moment it’s adding nearly 16 minutes to the processing time!

2

Hi

I do not know if I understand your case 100 % but I would suggest to make a separate module “createExcel”. In that module you check if there is a master total. If true, then you create an excel with the sheet. If it is not there, you do not and a different excel is created.
I think you also need to call this module using iterations in which you iterate over every client and call the module for each client.
Something like that.

Kind regards
Nikolaas

1 Like
3

Hi Nikolaas,

thanks for the suggestion - you’ve nailed it!

In order to delete the sheet I have a field in my Client Master List table to indicate if they have more than one product which I iterate on to decide whether that sheet is deleted or not - I can use this as the basis for the creation of the Excel file instead, so I can run 2 Excel File Creation Processes, then an append for the first “Product Totals” in my example where the Excel was created with the “Master Total” and then run my usual append process for other products all based on Client ID.

This also keeps my segmented processing methodology in tact making it easy for me to tweak each process if required!

Thank you.