I'm struggling with saving copies of Excel workbooks that have Sensitivity Labels that use encryption. This is my first time working with Sensitivity Labels so apologies if I'm not nailing the terminology.
IT at my company has set up Sensitivity Labels for Office. I think these are the same as Azure Information Protection labels. Some of the configured labels allow the workbook to be opened by anyone, while others restrict it to people with a company email address. I'm assuming from the literature that the latter are the ones that have encryption turned on.
I'm encountering weird behaviour as follows. In my workbook I apply a label that restricts access to our company (again, I think that means encrypted). Then in my VBA code I use SaveCopyAs to create a copy of the workbook for archiving. Here's a MWE:
Sub testSaveCopyAs()
ThisWorkbook.SaveCopyAs "C:\Spreadsheets\Copy.xlsm"
End Sub
The first time I run that snippet, it successfully creates a copy of ThisWorkbook. I can see it in the folder, I can open it and see it's got the same Sensitivity Label, everything looks fine. If I then run that snippet again I get Run-time error '1004':
Which makes it sound like the copy has been deleted or is locked by another program. And sure enough, if I look in the folder, the copy has been permanently deleted. Not sent to Recycling. When I run that snippet a third time now, it works again.
And that cycle repeats indefinitely: first run it creates the copy, second run the copy disappears and I get that 1004 error (not sure which of those is causal), next run creates the copy fine.
Now, to prove (I think) that this is not just some file system weirdness on my machine, if I apply a label which does not restrict access to our company (so, not encrypted I think), this issue does not happen. I can run that snippet over and over with no errors, it will just keeping saving a copy over the existing one.
What's happening here? Is this a known issue? It feels very much like a bug but maybe I'm missing something.
I acknowledge I'm not doing any checking for existence of the file before writing the copy. Hadn't gotten that far. Not sure if that's tied in somehow. But the fact that Excel doesn't mind over-writing the non-encrypted version makes me think that's a red herring.
Other Details:
- I'm running Edition Windows 10 Enterprise 64 bit and Excel 365 MSO (Version 2307 Build 16.0.16626.20198) 32-bit
- Behaviour is the same if I replace
ThisWorkbook
withActiveWorkbook
in my code snippet
Thanks.