Saving output .XLSX on SP site vs Parallel Block Until Done

Hello Everyone - I spent last 2 days trying to resolve my issue and searched multiple Community topics but unfortunately did not find the answer.

I am running a workflow which creates one XLSX output file with 3 different sheets inside (1, 2,3) - I attached the screenshot. I am using Parallel block until done to make sure sheets are being saved down in the correct order. It all works perfectly when output file is saved down on a shared drive but when I set it up to save down output on SP site there is an error when 3rd sheet is being saved down. Apparently it looks like there is a small delay between Sheet1 and Sheet2 but not enough delay between Sheet2 and Sheet3. Designer cannot open file to write Sheet3 because it appears file was not closed yet after saving down Sheet2.

Wait a second macro doesn't work as Sheet3 has 22k records so the wait time would be way too long, I tried Throttle as well but it doesn't seem to be delaying the last output sheet execution, only limiting number of records being processed.

Error text below:

Output Data (148) Unable to open file for write: <Sharepoint Path>: This file is checked out or locked for editing by another user. (220)

Did anyone have similar issue? Would you be able to help me please to find a solution?

WF.png

Output

Macros

Sharepoint

Accepted answers

magdaemem

Thank you both AkimasaKajitani and messi007 for providing me with some ideas. Actually I investigated it a bit more and it turns out it is really a Sharepoint problem - it locks the file for approx. 15 minutes after editing it so that is why it crashes when the 3rd output is being saved down. The sequence of events is Save down the file with Sheet1, close, open, Edit by adding Sheet2, close (lock) and then the problem starts. So no matter what I would do with Alteryx tools before outputs - it wouldn't work.

Fortunately it currently works perfectly with Shared Drives so I will need to stick to this solution and use inputs/outputs from/to SD when uploading workflow to Alteryx Server.

All comments

messi007

Dear @magdaemem,

Have you tried to split the workflow and then use the crew macro (Runner) in order to run step by step.

Hope that helps,

Regards,

magdaemem

Thank you for your prompt response. I just looked up some more information on the Runner macro you mentioned but as far as I understand it requires splitting the workflow into completely separate workflows that are producing Sheet1/Sheet2/Sheet3. The issue is that this is pretty big workflow using 9 data inputs. Moreover outputs are not completely independent but are using different data sets which are being processed throughout the entire workflow run. I am afraid that splitting it would either require rebuilding the entire workflow or just copying it & removing part of the outputs which does not seem to be too efficient. Is my understanding correct?

AkimasaKajitani

Hi @magdaemem

Have you tried putting in the BlockUntilDone tool before all the data output tools?

magdaemem

Thank you messi007 and AkimasaKajitani. Isn't my Parallel Block until done I used supposed to do the same? Am I wrong?

My issue is around timing of writing Sheet3 after Sheet2 but order itself is already sorted out within the workflow as sheets are being saved down correctly when I use a drive as an output directory. Problem starts when I am saving it on SharePoint site.

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