I have a workflow that outputs to 5 different ranges within the same .xlsx file. I get an error that says "Unable to create backup of ****.xlsx to ****xlsx.bak: Access is denied. (5)" only when running the workflow on the Alteryx scheduler. When I run the workflow manually, it runs successfully without any errors. I have tried several things in attempt to solve this issue but none of the following have seemed to work: * Wrote a script that runs before the workflow starts, that deletes the .xlsx.bak file * Added Block Until Done tools, so that all outputs will not output at once * Added Wait A Second, so that all outputs will not output at once * Broke the workflow into 5 different workflows; one for each output * Changed the Runtime configuration, so now "Use AMP Engine" is enabled * Created a new .xlsx file and file name for the output. This resolves the issue for one scheduled run, but I continue to get the error for subsequent scheduled runs Any thoughts as to what may be causing this error or what could resolve the issue?

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Output Error: Unable to create backup of .xlsx to xlsx.bak: Access is denied. (5)

rshenoy

I have a workflow that outputs to 5 different ranges within the same .xlsx file.

I get an error that says "Unable to create backup of ****.xlsx to ****xlsx.bak: Access is denied. (5)" only when running the workflow on the Alteryx scheduler. When I run the workflow manually, it runs successfully without any errors.

I have tried several things in attempt to solve this issue but none of the following have seemed to work:

Wrote a script that runs before the workflow starts, that deletes the .xlsx.bak file
Added Block Until Done tools, so that all outputs will not output at once
Added Wait A Second, so that all outputs will not output at once
Broke the workflow into 5 different workflows; one for each output
Changed the Runtime configuration, so now "Use AMP Engine" is enabled
Created a new .xlsx file and file name for the output. This resolves the issue for one scheduled run, but I continue to get the error for subsequent scheduled runs

Any thoughts as to what may be causing this error or what could resolve the issue?

Datasets

Output

Scheduler

Accepted answers

All comments

mceleavey

Hi @rshenoy ,

this sounds like a permissions error. Is the user that is being used on the scheduler the same as the user that is logging into the machine, or are you using a service account?

If the user on the Scheduler is different, then it looks like that user does not have write permission to that folder. For example, if you are using "My Documents" then only your username will be able to write to that folder.

To check this, change the user running the scheduler to your username and try that, or change the folder to which you are writing to a common shared folder.

ChrisTX

If you don't have security permission errors.... you may be running into a write-contention error, where Alteryx is trying to write to the same Excel file at the same time. I had a workflow that ran OK when I was writing to an Excel file on my desktop, but generated errors like "cannot create backup" when writing to a network folder (likely because of a slower write speed) .

It's very frustrating, and solid solutions are rare.

In this post I uploaded a macro and sample workflow I use to avoid errors when writing to one Excel file. I *still* get occasional errors with this approach, but they are rare.

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

And in this post I explain how to configure multiple calls to the macro:

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

And if you'd care to add a Like to my related Idea, where I'm asking Alteryx to help make it easier to write to one Excel file without write-contention errors: https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Enhance-options-for-Excel-output/idi-p/843260

Chris

EdP

Please see the following as well: https://knowledge.alteryx.com/index/s/article/Error-writing-Excel-file-to-Shared-Drive

mzak89

Hello!

I was able to fix similar issue by closing the output tool inside the batch macro like in the attached file.

With the file name as the control parameter. This approach ensures that the records are being processed one by one and worked for me.

Kind regards
Mateusz

batch macro.jpg

CharlesW

Hi all, this was supposed to be fixed in the new version with Control Containers, unfortunately it still happens.

Runs fine sometimes while the first time I try to run on the scheduler it fails because it cannot create the .bak file.

I've changed from using parallel block until done (which doesn't work using AMP engine)
I use control containers to control and write only one tab at a time. (which requires you to enable AMP engine)
I run powershell after every run to remove the .bak file
It's not a permissions issue because if it can write the excel file in the first place it could write the .bak file.

So at this point, I just cross my fingers and hope today it my lucky day.

ChrisTX

When you link Control Containers that write out Excel sheets, have you tried adding a CReW_DelayASecond macro?

I haven't had any problems after I started using Control Containers.

CharlesW

No sir, not yet. I removed those because they always cause error on save to server and shouldn't need them when you are using control containers and technically Crew macros are not supported on server.

Thanks for the idea though!

I'm hoping the issue was the .bak file was being deleted before it was finished. - Which also shouldn't happen since the even was set to "after run"

So I've changed my powershell, to target a parent directory and just delete recursively through the child folders. It takes a little longer for powershell to run through all the child folders (10 seconds).

I'm not sure if that'll fix it, Because, it usually finishes locally, It's scheduled for 4:30a tomorrow. We'll see how it goes........

It's when I run these workflows on server using a schedule that things seem to get unpredictable.

EdP

Hi @CharlesW

Please see https://knowledge.alteryx.com/index/s/article/Error-writing-Excel-file-to-Shared-Drive

We're finding some environments have replication or virus scans that run and lock the BAK file, which causes Designer to error on the file. Adding a delay alleviates the issue. I suspect the Powershell script is adding a similar delay as the DelayASecond CReW macro and serving the same function of giving a moment for the background process to complete the replication or scan and unlock the file.

If you'd like to open a case, you can ask it be directed to me, Ed Phelps, and I'll be happy to work on this issue with you.

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15907

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703