Output to Excel file: Create sheet if it does not already exist

[nhunter]

I am writing output to an Excel file within a batch macro.

If I use the 'Create New Sheet' option, it writes the first time the macro is invoked, but fails afterwards with a 'Sheet already exists' error.

If I use the 'Append to Existing Sheet' option, it fails the first time with a 'Sheet does not exist' error.

Ideally, I would like the sheet to be created the first time the macro is called, then appended to on subsequent calls.

Assistance would be appreciated.

Thanks.

[LordNeilLord]

Hey @nhunter

"Overwrite Sheet (Drop)" is the function you are looking for!

[nhunter · Accepted solution]

Thanks @LordNeilLord, but won't that will drop the sheet each time the macro is run? If so, I'll end up with just the values from the last iteration. [Edit: I checked. It does] I want all the values from all the iterations. Sorry if that wasn't clear.

I tried filtering on Engine.IterationNumber, create the sheet when 0, append to existing if > 0. Unfortunately, this only works if there are some records to write on the first iteration, which is not always the case.

I guess I could pass the data I want to write back to the calling workflow in a Macro Output tool. That way I would get all the data from all the iterations in one place.. Seems like a bit of a hack but not the ugliest design choice I've had to make.

[LordNeilLord]

Ahhh I see thanks for clarifying... I'd be inclined to go with your second suggestion (I would have tried your first suggestion first!)

Let me know if you get it working

Neil

[david_fetters]

If you're trying to append records to the same sheet inside a batch macro, is there a reason why you can't just output all the results from the batch macro and then output them to into the excel workbook?  I.e. instead of outputting inside the macro, output once you have the results of all the batches?  I think that's a more traditional use of the batch macro, but requires that schemas not change between runs (which would cause you problems inside the excel workbook anyway).

If what you want is to create a new sheet after each run, that's pretty trivial to do provided you can assign a sheet name as a value in your datastream and have it be unique between runs.  Hard to know what will work best without some more info on your use case.