How to write to different tab on same excel file

[johnnykoung]

Hi,

I created a workflow that I want to output to one excel file but on different tabs for each output. I'm running into an error that says it's unable to open the file because it's being used by another process. I'm assuming that this is because the file is being written on at the same time by the other output in the same workflow.

What can I do to fix this, so that I have one workflow with two outputs on different tabs of the same excel file?

Thanks,

Johnny

[JosephSerpis · Accepted solution]

Hi @johnnykoung are you using the Block until done tool before the Output Data tool as this usually helps me when I'm writing to an Excel file and output to multiple tabs in the same file? 

[AlteryxUserFL · Accepted solution]

To fix this, you need to use a block until done tool or the crew macro parallel block until done tool. This will stop Alteryx from trying to access both sheets at the same time. 

[Jonathan-Sherman · Accepted solution]

Hi @johnnykoung,

There are a couple of ways to do this,

Method 1: You could take advantage of the batch output method at the bottom of the output tool. Select the checkbox "take file/table name from field" and choose "change file/table name" in the dropdown. Finally select the field you want to use as your sheet/tab name in the dropdown (i've chosen category in my example below). This will output the row of each unique category into a new tab (Office Supplies, Technology etc).

Workflow:

Excel File:

Method 2: You could use a block until done tool to only run one output tool at a time and take the sheet name from a field (i've created a custom sheet name in a formula tool)

If this solves your issue please mark the answer as correct, if not let me know! I've attached both methods for you to download if needed.

Regards,

Jonathan

[ahmed1]

Thanks DrewDavis

[ChrisTX]

I've used the Block Until Done tool before, and the workflow still generated errors when writing multiple sheets to one Excel file.  One of the errors was "unable to create a backup copy of xyz.xlsx".

The only option that seems to work for me every time is to use a batch macro, plus a Wait a Second macro, to write different formats/schemas to multiple Sheets in one Excel file.  But this method adds to the runtime, by using the Wait a Second macro.

 

Please consider adding a Like to this related idea:  Enhance options for Excel output

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enhance-options-for-Excel-output/idi-p/84326...

Chris