Delete all existing tabs in output

Question

Hi

I am trying to create a file which has multiple tabs based on a field in the dataset. I have some filters in place as sometimes I only want 2-3 tabs but other times I want the whole dataset (around 20 tabs). If I run the data using the filters, I want to delete all existing tabs in the output and replace them with tabs for the filtered data only.

Ideally, I'd like to delete all tabs and replace them with new tabs every time I run the workflow.

Any advice appreciated.

Thanks

James

binu_acs · Answer

@JamesHowells1984 One way of doing this with the batch macro

DynamicFilter_120623.yxmd

mceleavey · Answer

Hi @JamesHowells1984 ,

You're writing out via the output tool so you should build a batch macro which contains your output tool. The batch will be every filename complete with sheet name, for example C:\Users\James\Documents\Excel File.xlsx|||Sheet1

The issue you will have is that if you are overwriting you will only end up with the last one that ran. To get around this you will need to overwrite the function in the output dynamically depending on the engine iteration number. If this is zero, overwrite the file. If not, add the new sheets. This will have the effect of wiping the file on the first run and creating a file with a single sheet, then the next sheet will be added as a new tab rather than overwriting the file.

Happy to jump on a call and discuss if you need to.

M.