Multiple Outputs to Xlsx - How to Order Writing to a Single File?

I have a workflow that takes several different data sources, cleans/combines the data, and then writes the data to a single output file (xlsx) with various tabs based on the data.

For example:

All sources (1, 2, 3, etc.) -> cleans/combines data -> Output to a single xlsx file with 30+ tabs (file.xlsx|||tab1, file.xlsx|||tab2, etc).

Problem ->

I continue to receive the error, "Unable to make final write to [file.xlsx], error in use by another process." The workflow is creating the single xlsx, writing to that file, and then erroring out because Alteryx already has the same file open.

Possible solutions ->

I've tried to use the "Block Until Done" and "Throttle" tools but Alteryx Designer doesn't actually wait to open the file. Is there a way to order the outputs for the various tabs? Is there a way to chain the outputs or wait until the file is closed to start writing to the file?

I know another solution would be to combine all of the outputs into a single output, but I'm guessing that there is another solution that I haven't thought of.

Workflow

Output

Accepted answers

Brian_Stoffel

@DataNath No, you're correct. My workflow roughly follows that forum post.

I think I figure it out. The issue with my workflow is due to network lag. Alteryx Server can run my workflow without error and if I cache all the inputs, then my workflow runs without error.

All comments

DataNath

Hey @Brian_Stoffel I recently built a macro to service this request. Does this help? You would just select the 'multiple sheets' option in the configuration and then use the Field that you want to split on as your grouping field:

https://community.alteryx.com/t5/Public-Community-Gallery/Output-to-multiple-Excel-files-or-sheets/ta-p/951995

Brian_Stoffel

@DataNath I appreciate the assistance with the Macro. I try to avoid non-standard tools/macros because Alteryx Server would be running this workflow in the future. I'm not sure if Alteryx Server allows macros or if that is my company's policy on not installing macros on Alteryx Server.

DataNath

You wouldn't necessarily need to install this on server, you could just integrate it into your workflow and then make sure it's packaged as an asset when putting it on server. If that's still not allowed, you could still just use the manual formula to create filepath approach (which is all the macro is automating anyway), as detailed here:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-output-multiple-tabs-within-a-single-Excel-file-based-on/td-p/507828

Your dataset will need to be in one for this to work so apologies if I've misread your requirement.

Brian_Stoffel

@DataNath No, you're correct. My workflow roughly follows that forum post.

I think I figure it out. The issue with my workflow is due to network lag. Alteryx Server can run my workflow without error and if I cache all the inputs, then my workflow runs without error.

EdP

Additional suggestions can be found here: https://knowledge.alteryx.com/index/s/article/Error-writing-Excel-file-to-Shared-Drive

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167