Issue with writing output to multiple tabs within same workbook

The attached workflow writes four outputs...each its own tab...onto a single excel workbook. Block until done tools are included to drive sequencing of how the output was written to avoid contention/errors. This workflow is able to reach a good end on my old laptop which uses Windows 10, but I was recently upgraded to a new laptop which uses Windows 11. When running this workflow from the new laptop, I'm getting the "...The process cannot access the file because it is being used by another process." error message. My efforts to trouble-shoot thus far include staging the output file as a shared workbook, swapping out the Block Until Done tools with Control Containers, and turning on AMP Engine / Engine compatibility mode...but nothing seems to work. Also attached is the workbook I'm trying to write to. Any advice on how to resolve?

Dealer Returned Fees.yxwz

Dealer Commission.xlsx

Data Investigation

Accepted answers

KGT

@Jake5, the suggestion from Chris on Batch Macros will be the best to solve the issue and encourage you to take a quick look around on the community for examples, like this. However Control Containers may also work, I just haven't used them for this.

There is something you can try on your workflow though.

Turn AMP off for this workflow. You don't need parallel processing on this and as your trying to slow parts down, it'll just be easier to turn off AMP.
Put the BUD closer to the end.
The way the BUD Tool works, is it pauses until it has all records, then starts feeding out Output 1 (some people debate the order, but I've always had it go 1,2,3), and once the last record has left through output 1, the records start exiting through Output 2, and once the last record has left, they then start exiting out Output 3...

In your workflow, there is a good chance the BUD Tool is complete on all outputs before the rest of the data is at the append, so the S node is already there waiting on both appends.

All comments

aatalai

Possibly try putting the output files into control containers and see if that works. Also, is the file directory still correct with the update?

mceleavey

Hi @Jake5 ,

you will always get this problem when trying to write to the same file and using the Block Until Done tool in an attempt to run it in sequence. This is not really what that tool does.

The problem you're having is Excel is still open when you're trying to write the next tab out to that file.

The best and most consistent way to to do this is to put the output tool into a batch macro and use a control parameter to overwite the tab name in the file. What the batch macro will do is ensure the file is closed at the end of each batch process, and so the Excel problem will go away.

You can achieve the same by using four separate outputs and placing them into control containers, then sequencing by attaching each control container to the next one. This method, however, is hard-coding a set number of tabs each times, so if this is unlikely to ever change then you can do this, but the batch macro will automatically add a new one should that ever happen.

Let me know if you need any further help with this.

Jake5

Thanks for the input. I'm unfamiliar with batch macros but attached is this workflow using control containers and the results are mixed. More instances than not, the workflow continues to error out. And to answer your question from earlier, the formatting of this workbook does not change, so I'm uncertain why the control containers are not working. Are you on Windows 11? If so, are you able to run my workflow to where it consistently reaches a good end?

Dealer Returned Feesv2.yxwz

davidskaife

Hi @Jake5

I'm on Windows 11, and can run it successfully as a workflow or an App - tried it multiple times with no "...The process cannot access the file because it is being used by another process." error message

KGT

There is something you can try on your workflow though.

Turn AMP off for this workflow. You don't need parallel processing on this and as your trying to slow parts down, it'll just be easier to turn off AMP.
Put the BUD closer to the end.
The way the BUD Tool works, is it pauses until it has all records, then starts feeding out Output 1 (some people debate the order, but I've always had it go 1,2,3), and once the last record has left through output 1, the records start exiting through Output 2, and once the last record has left, they then start exiting out Output 3...

In your workflow, there is a good chance the BUD Tool is complete on all outputs before the rest of the data is at the append, so the S node is already there waiting on both appends.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317