Output to multiple sheets within an Excel file using formula driven file name

[DBourke19841]

Hi,

I've been using Alteryx for about 6 months and love the tool. I have come across a new issue with one of my workflows which I'm hoping to get some help on.

I am creating 3 output sheets which write to a single excel file on a share drive. 2 of my outputs run as normal and save into the excel file location but the third output is returning the following error message "Unable to Open archive for zipping <File Path> The System cannot find the path specified".

As the file path is the same for the other two outputs which run without incident and the output settings are the same for all three outputs why am I getting this error?

Any guidance would be much appreciated.

Thanks,

Donal

[Thableaus]

Hey @DBourke19841

Are you using a Block Until Done Tool? How is your output tool configured, could you show us?

Cheers,

[ShawnM · Accepted solution]

Block Until Done will get you part of the way there.  But you best bet is using the workflow to generate a file path that includes tab (sheet) name.  This will allow you to generate multiple tabs to one document, without that error.  You can use a formula tool to either create an entire file path, or generate a file path using the source data (like in my attached example).  The Output Data tool allows you to use a file path in the data to write to a specific tab in a file, and you can elect to remove the file path from the output data for cleanliness.  Please see the image below and the attache workflow example.

 

[DBourke19841]

Thanks for the feedback guys.

@ShawnM the workflow you have below is exactly what I have been using to generate the file path and output. My source information is coming from three sources which come through my workflow with various exclusions built in. I then joined these data stream to the single book using the formula tool to generate the file name.

The output is writing successfully for two of the three data sources and I can't figure out why.

I have tried the "Block Until Done" tool also but the error still persists for this single data stream.

Really appreciate your time on this.

Thanks,

Donal 

[ShawnM]

Is your workflow something you can zip-up and share?  It might be a simple Output configuration issue that can be resolved with a fresh set of eyes.

[DBourke19841]

Hi Guys,

Looked at it fresh today and it was an issue with some source data mapping which wasn't captured in the output which was causing issues.

Thanks for your time.

Donal

[haphan_tran]

I write a deep dive into this problem in this post. Please take a look at this.
Writing to multiple Excel Sheets in the same files... - Alteryx Community