Writing Multiple Sheets to Same File -- Unable to Open archive for Zipping

[BLewis]

I'm attempting to split out several different inputs into 1000 worksheets, with 2 tabs each.  

I can write to 1000 worksheets with 1 tab easily (using the formula tool to write to C:\X\X\X\[name].xlsm|sheet1).  However, when I add the second sheet (formula - C:\X\X\X\[name].xlsm|sheet2) and attempt to write to that in a second output data module, it fails after writing some of the records (less than half).  

The error I get is unable to open archive C:\X\X\X\ZAS(###).tmp [The ### portion changes each time].  Error Opening File:  C:\X\X\X\X\ZAS(###).tmp : Access is denied.  

I have tried throttling, as well as utilizing a block until the first set of outputs is done, but I can't get it to work.  I also need to use two separate outputs because the two data sets I'm outputting to each file are fairly different in format, so they can't be merged onto one.

Thank you so much for your help

[Inactive User]

Use the CreW Macro Label Block until done. The first path write the sheet 1 files. In the second path write sheet 2 into the sheet 1 file. Block until done can have this issue; try using the macro I mentioned as a blocking tool works better in my opinion.

[BLewis]

Ryan --

I tried utilizing both "Label Block Until Done" and "Parallel Block until Done" in a variety of different positions and had no success -- it still fails after approximately half of the records.  Do you have any other suggestions?  Thank you for your help!

[MarqueeCrew]

https://meetings.webex.com/collabs/meetings/join?uuid=M04MRYVSOSV7X92L8DFYYP50V8-UCSL

I will help you with the challenge now if you would like.  You can join me in my meeting room.

Cheers,

Mark

[estherb47]

@BLewis, have you tried a Block Until Done tool before each output tool? I've found, in many instances, that just adding that tool before each output (streaming only from the 1 output node) works when trying to write multiple sheets into the same file (has worked for up to 6 sheets being written into the same .xlsx)

Basic, but hopefully useful! 

Best regards,

Esther

[bfarley]

Interesting, as I have never had an issue using Block Until Done (placing it just before the output). 

[MarqueeCrew]

Ben,

With 1 record per sheet, the Excel writes were competing for access to your file.  By placing a Throttle after your Parallel Block Until Done (CReW Macro), I think that your challenge is solved!

Cheers,
Mark

[BLewis]

A very strange update on this:

Worked with Mark a little and attempted throttling to fix the issue.  Sadly, that didn't resolve it. 

Instead, the issue appears to be present even when writing it all in one sheet.  back to earlier versions of the file that worked previously - I had a version that would write only my first data set to each workbook, so 1 sheet x 1000 workbooks.  Now it fails partway through that, yielding the same "Unable to Open archive for zipping: C:\X\X\X\ZAS5F12.tmp Error Opening File C:\X\X\X\X\XZAS5F12.tmp".  Doesn't appear to be related to writing multiple worksheets to my computer.  As far as I can recall, these were functional workflows previously, and I cannot explain the issue.

And another round of testing:  It appears to work on the first iteration, but not on subsequent ones.  If I delete all files and start over, it seems to work.  Note that this doesn't work in the case of writing sheet 1 and sheet 2

My current hypothesis is that this has to do with accessing the excel files, but I'm confused as to why throttle and block until done haven't solved it.

[Sugu]

Hi Lewis,

Did u figured out the solution for the error, we too face the same issue. We tried adding 'block until done' but not worked..

Regards,

Sugu

[bfarley]

BLewis -  Have you tried configuring Alteryx to use an external drive through your USB?  It might be worth a try as it your error looks to be touching a hard-drive issue with all the data writing and reading.