Can't write/access excel files with multiple tabs -and crew macro doesn't work

[ck2024]

I have the Crew macros in place and up until recently if I used the parellel block until done I was able to write to an Excel tab, and then it would go on to the next anchor to allow it to write to another tab in the same file.  However, nowadays I am getting the error 

 Output Data (215) Unable to open file for write: ... The process cannot access the file because another process has locked a portion of the file. (33)

 The only solution I can use is to use the Crew Macro wait a second, but the issue here is that instead of waiting a second it takes like ten minutes to run a process that runs in seconds if i write each tab individually by connecting and deleting each anchor in turn.  

I can't use the block until done because the tabs are feeding off one join tool - or at least i think that's the case unless someone has a solution!

I can't share the workflow as it is sensitve data

Thanks

Cass
 

[mceleavey]

[EdP]

Additional suggestions can be found here:  https://knowledge.alteryx.com/index/s/article/Error-writing-Excel-file-to-Shared-Drive

[sophabraham95]

Hi @mceleavey 

your suggestions in this post are very thorough!

I've implemented a combination of both your first and second options mentioned, with block until done and the batch macro to write to different sheets of the same excel.

But still I get an error sometimes. and sometimes it passes. the error occurs randomly.

could you please help me understand why?

Ive attached the batch macro that writes into different tabs of same excel file below as well.

error message:

Error: ResidualMacro2 (410): Record #10: WriteToTabMacro (96): Record #3: Tool #14: Unable to create backup of \Output\AAAAAAAA_ALTERYX.xlsx to \\Output\AAAAAAAA_ALTERYX.xlsx.bak: The process cannot access the file because it is being used by another process

[cjaneczko]

@sophabraham95 by any chance, is the file you are writing to in a OneDrive folder or backed up by OneDrive? I have found that OneDrive starts to sync the file before it is completely done writing all of the tabs. If you Pause OneDrive during the workflow run, it should prevent the errors. Once the workflow has completed, you can resume OneDrive syncing.

[mceleavey]

Hi @sophabraham95 ,

You can forget the Block Until Done tool, you need to create a batch macro that writes out to the file(s) you want. The control parameter will be the full filepath including the |||<Sheet name> on the end. This will ensure you are writing out to a file, then closing it out before starting the next. You should then avoid the issue where it's trying to write the next one without closing out the first.

M.

[sophabraham95]

@cjaneczko thank you for your response, the folder is not synced with one drive.

[sophabraham95]

@mceleavey  that is exactly what ive done. But I still get the error. the thing is, it is not an unable to access excel error. It is an unable to access backup file error. Which is what is stumping me. Ive attached my batch macro. let me know if I should be changing anything.

The error occurrence also seems random to me. Sometimes it runs successfully, some times one of the tabs fail to write due to the backup error.

Macro Config:

[Jackf99]

Did you ever figure this out? I am having the same issue