This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
09-23-2016 03:30 PM - edited 08-03-2021 12:57 PM
Have you ever tried writing to multiple tabs within the same workflow and have received an error like this:
“Error: Output Data: Unable to open file for write: FULL_FILE_PATH.xlsx Error Opening file: FULL_FILE_PATH.xlsx: The process cannot access the file because it is being used by another process.”?
This is occurring because Excel, like many applications, will get confused if multiple processes are happening to it at once which is why you can’t have the same excel file open while you are reading it in to Alteryx. To avoid simultaneous writing, there is an awesome tool called the Block Until Done Tool which will prevent this.
The Block Until done tool will wait until all processes upstream are finished before sending work downstream. This tool has three outputs. What is useful about it to us in this case is each output will be processed in sequential order. To illustrate:
This will always error out because the output tools in Alteryx are being used at the same time thus Excel will get confused.
In contrast this will always work because the second stream will not kick off until the first stream is finished.
So all youhave to do when writing to the same file but different tabs is make sure youuse a Block Until Done to prevent it from writing to the excel file simultaneously.
For more information on the Block Until Done Tool click here.
Thank you so much for this-- you've just condensed three separate logs (my stupid workaround of this problem) into one log that I can reasonably graph! Thanks!
Wow I have no shame, I'll ask the most basic question. How do set up the output excel file to have multiple sheets, sheet 1, sheet 2, etc... This is the solution I'm looking for to combine multiple flows into one excel rather than several. thank you
This is so helpful!
Hi there! I have a similar problem that can't seem to be solved with the Block Until Done tool. I set up one workflow that has multiple outputs exported to different tabs on the same Excel file. The ones that I chose to "Overwrite Sheet" worked, while the ones with "Append to Existing Sheet" gave me the error of "unable to open to write for file: ... the file is being used by another process." I put the Block Until Done tool before the Output tool and the same error happened. Does anyone know what's wrong? I attached a screenshot, in case that is of any help.
Thanks!
Hi All,
I think I'm having the same problem as @serhoshu1118. If the workflow uses a filter to separate the data into two sets, each of which goes to a different sheet in the same workbook, the Block Until Done can't be used.
Unless there is a better solution (anyone?), it looks like the only option is to put the each output tool into its own Tool Container and disable one while the first is run, and then re-run the whole workflow with the first output disabled and the second one enabled.
@asteryx - you can use Parallel Block Until Done (from the CReW Macro Pack) to accomplish this.
Better late than never?
Returning to this thread as i think I've found a solution I wasn't aware of before.
In the Output tool, there's an option to specify the "table" to write to via a field in the stream.
I added a formula tool to each stream that named the sheet it is intended for. Then I used a Union tool to send them all in to the same output tool.
The records get written to the same workbook, but go to the sheet specified in the field.
I would suggest that this is a more common use case
It would be very useful if it where possible to build the "block until done" functionality into the filter tool because the following looks really ugly to my eye.
Perhaps there is a better alternative?
Unfortunately asteryx's solution above doesn't work because my output streams will have different data formats and so joining them with a union will add a large number of empty columns to each worksheet.
Just a little brainstorming in case it helps...
If your outputs have different schema, could you first output them to separate yxdb files and then use a final step to write each to the same Excel file?
The final step might work if it's a batch macro that runs the write once per yxdb.
I have a related question. How can I control the order of the tabs in my Excel output? My workflow has 3 separate flows and each flow generates a tab in the same Excel output. Then I have a Summary tab that summarizes results from each flow. When I look at the sequencing of the flow in the results window, I can see the tabs are getting generated in this order: A, B, C, Summary. But in the actual ouptput that gets posted, I see A, B, Summary, C.
Thanks in advance for your help!
I'm just guessing, but I suppose it could be writing based on which ever one is ready first.
If you're using my suggestion of adding the sheet name and writing from a single output tool, I don't see a solution, but if you go back to the other approach with a separate output tool for each sheet, it might work with a Stop Until Done tool.
So if I read this trail correctly. If I am writing 12 tabs in a single Excel file I may need to add 6 "Block Until Done", correct?
How can I use Block Until Done when outputting 4 different data streams from a Macro output?
You can only input 1 data stream into Block Until Done.
A coworker and I are getting this error. I created a flow that updates multiple worksheets in the same excel workbook. It works fine for me. I emailed the file to her. We updated all of the mapping locations. She gets this same error message. I am using
to specify the sheet. Why does it work for me and not her? We both have the latest available versions.
Quick thought: Are you both writing to the same Excel file? Maybe it's locked for editing by you while she's trying to write to it.
No. The file I write to is on my desktop. The file she writes to is on hers
Hello everyone,
I am now automating a process and have run into the issue of writing multiple tabs into the same excel sheet. I tried using the Block Until Done tool however it was of no use in my case (or I have placed it in the incorrect place). As you can see in the picture below, my workflow separates into 3 different flows at some point; each of these will have a unique output that I will need to write into a different excel sheet.
Please note the following:
- The excel sheet does not exist in the first place and I was to create it in the first flow and then write to it in the following 2 flows (In case this is not feasible then I can overwrite and existing excel sheet)
- The Block Until Done tool does not seem to help in running a sequence for each of the paths (run the second after the 1st if fully done, and the 3rd after the second is fully done), which is leading to the error below.
- Error: Unable to open file for write: C:\Users\rmjamil001\Desktop\12.xlsx Error Opening file
: The process cannot access the file because it is being used by another process.
Thanks alot for your help!
I just used the "Block until Done" tile and the workflow then output each tab as expected. A potential problem remained in that the design had a few branches and then could have presented a problem.
This is not really good enough and a more elegant solution is required.
The block Until Done doesn't always work because I save my files on a Shared Drive where I am guessing there is some lag when files saved. There fore I have to do the following trickery. Alteryx should implement a block until done with "Time lag" feature that we can choose seconds to wait. The left workflow does work fine if I save files to my laptop but not a Server location.
Hi @JJH , @raedjamil and all the others!
I like the tool / macro introduced by @Ozzie indeed! Bookmarked it for future use.
For some of the cases described above maybe the macro, named "Parallel Block Until Done", our Alteryx consultant @DavidSta gave to us could be of help.
Basically it works like this (at least as far as I understood): the upper string goes though the "1" anchors and writes into the excel table, only when this is done the workflow will continue working on the string going through the "2" anchors. In case there are more than two strings you've to build a cascade with the macros.
(PS: picture above is no real workflow, just a bunch of tools to illustrate the set up.)
Unfortunately, I cannot upload the macro in this dialogue, but maybe this link -> Crew-Macro-Pack will finally lead to it.
Best
Alex