About the "being used by another process" error

Question

I have a workflow that outputs multiple sheets into one excel file. I know I'm using my BUD tools correctly and my workflow executes smoothly when I run it. But when I let someone else run it (the intended end-user) it seems to intermittently give the "being used by another process" error. But I noticed that their system runs quite slow compared to mine. Like when I run it it finishes in under 10 seconds. But when they run it, it takes around 30 seconds or so.

Could it be that one of the cause of this error is if the user's system is slow? Like I'm imagining before it can close the file COMPLETELY it lags (like when you try to close a big excel file and you're using your company's limited bandwidth and it takes a while before it's closed completely even if minutes have already passed by since you pressed the close button) but then the next part of the workflow keeps going hence the error. Does that make sense?

ChrisTX · Answer

I've been using that macro for a long time, and I've only had one instance where I ran into a "could not create backup" error, which may have happened after I locked my machine when the workflow was running.

Otherwise it's been a solid solution.  I've read so many posts, and tried so many different tests, to write out Sheets to one file, in order, without write-contention errors.  It's such a common need, and it "should" be easy.  Hoping the Alteryx team gives us better functionality soon.

Chris

justinihil · Answer

Hi @ChrisTX

Thank you for this! It took me a while to understand what the macro is trying to do but I eventually understood it and got it working on my end. Unfortunately, the end-user is still getting the write error intermittently, though. Guess I'll just have to live with this for now and hopefully I can make a case in the future to request my system be upgraded and have all alteryx workflows be run by me so that we minimize these types of errors.

ChrisTX · Answer

@justinihil I had the same problem, writing multiple sheets to one Excel file.  When I wrote the file to my machine, I didn't get any errors. When I wrote the file to a (slower) network drive, I would get file write-contention errors ("being used by another process", "could not create backup", etc).

I solved the issue by using Parallel Block Until Done and a batch macro.

See both of my replies in this post:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Clear-the-output-file-before-running-a-macro-and-setting-up-an/m-p/1040648

My first reply provides the macro and a sample workflow.

My second reply gives an explanation of how it works.

I've been saying for many months:

* This is a very common need, even for beginners
* Needing to use a CReW macro and a batch macro to write out different formats will prevent many users from developing a solution that works
* For such a common need, this “should” be easy

If you'd care to add a Like, here's my Idea for "Enhance options for Excel output":

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enhance-options-for-Excel-output/idi-p/843260

Chris