Excel waits a long time to start saving the document

Question

When opening my sheet, I create a backup file of the current workbook almost without any delay.

When I close the file, another copy of the workbook is created also almost without any delay.

When saving the master file, Excel waits a long time (sometimes it takes several minutes) before saving.

The master file is not large, only 1.05Mb. I tried speedup solutions available on the net. Even tried reinstalling Excel.

This is the coding.

Private Sub Workbook_Open()
  ... ' perform some actions like setting NewName and ext
  ActiveWorkbook.SaveCopyAs (NewName & " (backup)." & ext)
  ....
End Sub

Private Sub Workbook_Deactivate()
  ... ' perform some actions like setting NewName and ext
  ActiveWorkbook.SaveCopyAs (NewName & " (backup)." & ext)
  ....
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If SaveAsUI Then Cancel = True 'Excel will close and handle saving itself.
  If ActiveWorkbook.Saved Then Cancel = True ' Cancel saving when no changes were made
  ...
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.DisplayAlerts = False  ' Make sure no close message pops up from the application
  ActiveWorkbook.Save
  Application.DisplayAlerts = True
  ActiveWorkbook.Saved = True
  .... ' Processing stuff amongst with resetting ScreenUpdating, EnableEvents and Calculation 
  Cancel = True ' Or it will fire twice for some reason
End Sub

Upon closing Excel I let it handle all the saving itself.

The workbook and copies are stored on a file server via a 1Gb network. The network isn't the problem, since the copies are saved very quickly. It is the master file that is saved sluggishly. It seems to be even slower when I work longer with the workbook.

I recently switched from Office 2007 to Office 2016 (=365). Before the switch there were no problems.

Answer 1

After extensive testing I come to this conclusion: the difference between saving a copy or the master file is that when the master file is saved, also temporary files and cash(?) is processed. This leads to extra delay before saving a file. This is the only reason I can find that explains the difference in behaviour between saving a copy or a master file.

Answer 2

You can test whether the file has already been saved before firing the save event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If SaveAsUI Then Cancel = True 'Excel will close and handle saving itself.

    If ActiveWorkbook.Saved = False Then
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        ActiveWorkbook.Save
        Application.DisplayAlerts = True
        Application.EnableEvents = True
        'Debug.Print "Fired"
    End If
End Sub

Answer 3

The Workbook_BeforeSave event runs every time the current workbook is saved. When it completes, the workbook is saved unless Cancel has been set to True (at any point in the subroutine). Setting Cancel = True stops the workbook from being saved when the subroutine finishes.

The problem is when you call the Save method within the Workbook_BeforeSave event, it immediately triggers the same event again, which then tries to save the workbook again and then triggers the event again. This is repeated until you reach the limit of the number of the nested sub-routines that can run simultaneously.

SaveCopyAs saves a copy of the workbook, which doesn't trigger the BeforeSave Event, so it just saves a copy of the workbook once quickly.

Maybe 2007 didn't allow this recursive calling of the Workbook_BeforeSave event or it had a smaller limit of number of nested sub-routines.

Setting Cancel to True doesn't stop the event in its tracks, it just stops Excel from saving the workbook again after the subroutine has finished. If your intention is to not manually handle the saving when there have been no changes (detected by SaveAsUI = True), then perhaps you should just exit the sub at that point:

If SaveAsUI Then Exit Sub

The subroutine will then stop without calling the save method, but the workbook will still be saved when the sub finishes, ending the recursive loop.