How to write to different tab on same excel file

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

Accepted answers

JosephSerpis

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

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

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

BatchOutput.yxzp

BlockUntilDoneOutput.yxzp

All comments

JosephSerpis

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

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

Hi @johnnykoung,

There are a couple of ways to do this,

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

BatchOutput.yxzp

BlockUntilDoneOutput.yxzp

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.

Excel output enhancement - must now use a Macro and Wait a Second.jpg

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/843260

Chris

Bpurcell2

FYI -- Didn't work until I turned off AMP Engine in the Runtime settings

ChrisTX

With a newer Alteryx version, you can use Control Containers.

Quick Links

This months top contributors

FláviaB 262

caltang 177

DanM 167

IraWatt 157

gawa 157