Batch Macro - Writing to Multiple Sheets within Same Excel - Unable to Create Backup Error

Question

Im using a batch macro to write to multiple tabs within a single excel file. And I get an error randomly at times.

Can you help me understand why. I have attached a sample macro for this use case and error message.

error message

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

Note:

AMP Engine is off

Batch Macro Takes filename as Control paramater

Creates a new sheet each time, not overwrites

WriteToTabMacro-v2.yxmc

jdminton · Answer

@apathetichell you're correct. My question of why use a macro still stands though 😀. I could see the same issue with the .bak if the output is being completed quickly enough, but I don't recall every seeing it specific to the .bak myself.

apathetichell · Answer

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Output-Error-Unable-to-create-backup-of-xlsx-to-xlsx-bak-Access/td-p/1077855

also points to potential temporary virus scan locking the bak file.

jdminton · Answer

That error message is the same as what you'd get when trying to use two output tools to the same Excel file without a block until done or control container. I'm curious why you are doing this as a macro. It should work fine having the output tool directly in the workflow. @binu_acs is correct that you'd want to change to overwrite if these files are going to be run more than once, but I'd put the output data tool directly into the workflow. There's no reason to use a macro here.

Output Data Sheets.yxmd