"Access is Denied" error when trying to update multiple sheets in Excel

Question

Hello,

I have seen similar posts on this topic, but none were exactly the error I am getting so I thought I would start a new topic for help.

I have 5 sheets in a single Excel worksheet I am trying to update. All 5 sheets are different data sets, so they cannot be a single feed that is filtered with each filtered stream adding a Block Until Done before the Output.

I built this module in 2015 and have been running it monthly and this is a recent error for me, and nothing has changed in the module or data, so I am wondering if this could be tied to the 2022.1 designer version?

I do not know if this is possible, but it would be nice to add a configuration to the Block Until Done tool, which allows the prioritization of the tool, daisy chaining them, so that for example Block Until Done #2 will not allow the data stream through until Block Until Done #1 is complete.

Any insights to this error and how to correct would be greatly appreciated.

Thanks,

Ray

ScubaGeek · Answer

Hello @Qiu ,

Thanks for the suggestion.

I tried that fix and it did not work. Based on a suggestion from @cgoodman3 I am going to add a new field to each data stream that is the output path to the specific sheet in the workbook. I will then union the streams for a single output. The issue with this approach is each stream has unique data fields and are not present in the other streams, therefore there will be a lot of null fields on each sheet with this approach. The suggested solution I am going to try is feed the union into a batch macro which removes the unnecessary field from each path prior to the output to Excel. Hopefully this approach solves the issue.

Thanks for your help,

Ray

Qiu · Answer

@ScubaGeek 
Maybe because the AMP enginer, refer the answer below from @MarqueeCrew .

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/The-process-cannot-access-the-file-because-it-is-being-used-by/td-p/948934