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

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!

workflow logic_2023-03-28_06-16-16.png

Error Message

Dynamic Processing

Accepted answers

mceleavey

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/ta-p/488165

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

I hope this helps,

All comments

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?

binu_acs

@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

Date_280323.yxmd

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 @binu_acs approach; the other is less so, but both will give you comparable outputs.

Hope this helps; let me know if not!

Multiple_Output_Example.yxmd

mceleavey

Hi @cwaletzko ,

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

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.

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/ta-p/488165

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

I hope this helps,

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703