VBA Workbook.Save in BeforeSave fails when file is shared. Why?

[EuroSong]

Hi guys,

I have a mystery which I'm hoping someone will help me solve. I have a workbook which needs to be shared. It relies heavily on VBA functionality.

In order to avoid save conflicts in certain specific cells which don't need to be saved (custom search boxes, stats dropdowns etc) I built a procedure whereby if a user clicks save, a BeforeSave event stores the user's values in variables and clears the contents of those cells; then AfterSave which restores the user's cell values. Effectively, the contents of those specific cells are not saved with the file (therefore no save conflicts), while the user gets to keep what's on their screen after they click save.

While the AfterSave works in Excel 2010, unfortunately my company's network uses Excel 2007, which as I later discovered does not recognise an AfterSave call. The solution, as found elsewhere, is to include everything in the BeforeSave call as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not ThisWorkbook.MultiUserEditing Then Exit Sub ' Do not do anything special if the "save" is only happening because the WB is being shared.
    ' DO BEFORE SAVE SUFF HERE
    Application.EnableEvents = False ' So the following line will not trigger this same procedure
    ThisWorkbook.Save ' Perform the save manually
    Application.EnableEvents = True
    ' DO AFTER SAVE STUFF HERE
    Cancel = True ' Cancel the native save function so the workbook isn't saved twice
End Sub

Now here's the problem: while this works perfectly well with a workbook in single-user mode - hijacking the native save function - it fails when the workbook is shared. The line "ThisWorkbook.Save" causes an error stating "The file is locked. Please try again later".

I can see no reason why this should be the case. The weird thing is that the error appears specific to the BeforeSave event. I have also tested it outside the BeforeSave event handler with a simple sub as follows:

Sub ManualSave()
    ' To test calling this when the workbook is already shared
    ThisWorkbook.Save ' Perform the save manually
End Sub

... and the above works without an error. So it's not that the Workbook.Save method fails on a shared workbook per se. Just when it's being called BeforeSave.

There are also more complex reasons why I need to use the BeforeSave event, aside from just clearing non-vital cells - including performing custom backups: so it's really not an option to stop using it. I have just distilled it down as above to focus on the problem.

Can anyone advise me? Thanks.

 

[Myconservator]

I might be completely missing the point here so please take this with a grain of salt.

When you are planning t run the second code:

Sub ManualSave()
    ' To test calling this when the workbook is already shared
    ThisWorkbook.Save ' Perform the save manually
End Sub

Close the work book on your computer.
Ask someone else to open the workbook on their computer.
Then open it in your computer.
Now try to run the code.

 

[EuroSong]

Myconservator: thanks, but what's that trying to prove? I do that, and it still works no problem. The "ManualSave" sub works just fine in shared mode, whether I'm the only one with the workbook open or not. The problem is with the BeforeSave event.

 

[Myconservator]

My logic (which is proven wrong now) was that it was related to saving while open at other location.

 

[EuroSong]

Ok - thanks anyway

A bit of extra information, for anyone else wanting to help solve this: it appears that the file locking might have something to do with Excel taking an exclusive handle on it at the beginning of the BeforeSave procedure. I'm not 100% sure this is the case, but I just experienced an issue whereby I was trying something in the BeforeSave procedure which exited upon a certain condition - only to find that the file lock remained in place. In order to remove the exclusive file lock, I had to terminate the Excel process.

I would have thought that the BeforeSave procedure executes in its entirety *BEFORE* any file operation. If my theory is correct, this is not the case. In that case then, why would the file being shared cause the lock, applied at the beginning, to cause an error while it does not do so in non-shared mode?

I'd be very interested to hear from anyone who has detailed knowledge of exactly what goes on with shared workbooks: at what stage does the Excel process access the file, and what sort of file locking is applied. Does anyone know?