Hello fellow Excel guys,
I'm a developer for my company, and we use a shared workbook which is accessed by several people through the day. It relies heavily on VBA (so much so that I have separated out the code into an .xlam add-in which is referenced from the main file, in order to keep the main file as small as possible).
Unfortunately, from time to time, when a user saves then something will go wrong with the save process, resulting in the main workbook.xlsm file being replaced with a small, 4KB meta file which does not contain any meaningful data. I do not know why this happens. I have implemented event logging in VBA which logs the username, timestamp and event to a separate file every time a file operation is performed, in order to try to nail down exactly what's happening. I am logging Workbook_Open and BeforeSave events. The system time stamp on the 4KB file corresponds to a BeforeSave event, so I know it's happening on save: however I'm at a loss to explain it. I know that these 4KB files are something to do with the regular save process, however occasionally the process will go wrong like this.
I'm not expecting anyone to be able to solve this problem from the information I have provided. However the above was a bit of background for what I want to ask. In order to mitigate the effects of the above failures, I have designed a custom backup system whereby BeforeSave triggers a backup script which keeps timestamped backups of the last 5 saves. I have also coded a system whereby a user may simply click a button on a portal dashboard to restore the live file from the most recent backup. However, if the user currently has the live workbook open, it fails.
I want to know if it's possible to deliberately crash out of a shared workbook. By "crash out" I mean that Excel simply unloads the workbook from memory *without* trying to write to the file to remove the "in use" marker. Because I know that with shared workbooks, when you close the file, even if you tell it NOT to save changes, it STILL wants to access the file in order to remove the current user from the UserStatus array. In VBA, [workbook].close Savechanges:=False will still access the file to tell it that the current user is out.
You can see when users have crashed out of a workbook when, for example, they have turned off their computer with the file still open and not closed it properly: a phantom session under their name will still appear in the list of "who has this workbook open" in the Share Workbook dialogue. Normally this is undesirable: however if the live file itself has been corrupt, then it's not possible to close it properly, therefore a deliberate crashing out is what I want. If I had a method to force-close a workbook without accessing the file in any way, then at least I could implement my backup-restore script properly without users complaining that it doesn't work because Excel won't allow them to close the workbook on a corrupt file.
I would be grateful for any insights. However please, PLEASE do not simply leave a reply with "The best advice is not to use shared workbooks - hahaha!". I am well aware of the disdain for the sharing functionality among the Excel users' community. Unfortunately there are no other viable solutions for what we need than a shared workbook.
Thanks.
I'm a developer for my company, and we use a shared workbook which is accessed by several people through the day. It relies heavily on VBA (so much so that I have separated out the code into an .xlam add-in which is referenced from the main file, in order to keep the main file as small as possible).
Unfortunately, from time to time, when a user saves then something will go wrong with the save process, resulting in the main workbook.xlsm file being replaced with a small, 4KB meta file which does not contain any meaningful data. I do not know why this happens. I have implemented event logging in VBA which logs the username, timestamp and event to a separate file every time a file operation is performed, in order to try to nail down exactly what's happening. I am logging Workbook_Open and BeforeSave events. The system time stamp on the 4KB file corresponds to a BeforeSave event, so I know it's happening on save: however I'm at a loss to explain it. I know that these 4KB files are something to do with the regular save process, however occasionally the process will go wrong like this.
I'm not expecting anyone to be able to solve this problem from the information I have provided. However the above was a bit of background for what I want to ask. In order to mitigate the effects of the above failures, I have designed a custom backup system whereby BeforeSave triggers a backup script which keeps timestamped backups of the last 5 saves. I have also coded a system whereby a user may simply click a button on a portal dashboard to restore the live file from the most recent backup. However, if the user currently has the live workbook open, it fails.
I want to know if it's possible to deliberately crash out of a shared workbook. By "crash out" I mean that Excel simply unloads the workbook from memory *without* trying to write to the file to remove the "in use" marker. Because I know that with shared workbooks, when you close the file, even if you tell it NOT to save changes, it STILL wants to access the file in order to remove the current user from the UserStatus array. In VBA, [workbook].close Savechanges:=False will still access the file to tell it that the current user is out.
You can see when users have crashed out of a workbook when, for example, they have turned off their computer with the file still open and not closed it properly: a phantom session under their name will still appear in the list of "who has this workbook open" in the Share Workbook dialogue. Normally this is undesirable: however if the live file itself has been corrupt, then it's not possible to close it properly, therefore a deliberate crashing out is what I want. If I had a method to force-close a workbook without accessing the file in any way, then at least I could implement my backup-restore script properly without users complaining that it doesn't work because Excel won't allow them to close the workbook on a corrupt file.
I would be grateful for any insights. However please, PLEASE do not simply leave a reply with "The best advice is not to use shared workbooks - hahaha!". I am well aware of the disdain for the sharing functionality among the Excel users' community. Unfortunately there are no other viable solutions for what we need than a shared workbook.
Thanks.
