Excel Multiple Tabs Error 'Unable to open file for write"

[cwaletzko]

Hello,

I'm creating a single excel file with multiple tabs, and I'm occasionally getting this error.  Any ideas how to resolve this?  I'm using the block until done conditions, and I'm still having issues.  Any help would be appreciated.

'Unable to open file for write:' '- Can't open file:'

Thank you!

[peterr_h]

Are you able to attach an example workflow including input/output tools? This would be easier to work from :)

[cwaletzko]

Unfortunately, I can't share my workflow because my company has restrictions on sharing.  Does it matter if the block until done tools were added to the workflow after the output tools were created?

[binuacs]

@cwaletzko If you are creating a single file with different tabs then you don't need to mention the output separately instead create a sheet name which is unique to each tab and update in the path. attaching a sample workflow for your reference

[cwaletzko]

This approach worked about 90% of the time when the workflow was initially implemented.  When it fails, it fails on the gallery if that makes a difference. 

[peterr_h]

Hi @cwaletzko , sorry for the delayed response.

Please see attached a very basic workflow for two different approaches you could take here. One is similar to @binuacs approach; the other is less so, but both will give you comparable outputs.

Hope this helps; let me know if not!

[peterr_h]

As for the failure on gallery, I'm afraid I'm not sure here - I had similar issues when creating bespoke outputs for a client recently. For some reason, trial and error led me to remove one Block-Until-Done tool and then the workflow functioned perfectly every time as expected, and I had no idea why!

[mceleavey · Accepted solution]

Hi @cwaletzko ,

Just to add to some of the suggestions made in this thread and to expand on the problem, the reason you are getting this error is because you are attempting to write to a file that is still open following the previous write attempt. Some people will suggest you use the Block Until Done tool but this is not fool-proof and NOT the best method to ensure you avoid this error.

The best way to do this, and the way you should be building this for server deployment is as follows:

1. Create a field which dynamically adds the tab name to the full path of the output. Do this in a formula tool and build up the fullpath with as many dynamic elements as you need. In the following example I've added filename and tab name as fields:

2. Use a "Summarize" tool to group this field. This has now given you a unique list of all files and individual tabs within the file to which you are writing.

3. Put your output tool into a batch macro with the Control Parameter overwriting the "Write to File or Database" field.

4. Connect your summarise tool to the control parameter.

This will now loop through each output and will ensure the process has closed before the next process runs. This will prevent two processes attempting to write to the same file at the same time.

The reason this is a better method than the Block Until Done method, is it does not matter how many tabs you have, and so you're not restricted to, say, five streams. You can output to a single file with a thousand tabs and you will never get the error as each process runs sequentially according to the order in which the control parameter is populated.

If you want to know more about Batch Macros you should check out the following resources:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Getting-Started-with-Batch-Macros/t...

https://help.alteryx.com/20223/designer/batch-macro

I hope this helps,

M.

[jessefu]

I found that the same workflow that used to not have the problem started having this when I turned on AMP for the workflow. Try turning off AMP for the workflow and see if that solves it.

[EdP]

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