File is "Locked for editing by another user" "Locked for editing" while using autosave.

[Mette85]

Hello,

I`m running a excel dokument that`s beeing used by 3 people randomly trough the day. When the dokument is opened it will start autosaving every 10 minute.. but i am getting error message when the "timers" meet or one of them opens the dokument at the same time. is there any way to ignore the message, and try to autosave again? without the meacro to stop running? atm i dont remember the error message, but i think its "Locked for editing by another user" or "Locked for editing". Reason for not posting the dokument is because the information on that one cannot be shared because its a facktory dokuement

This is the macro i`m running
Private Sub Workbook_Open()
SaveThis
End Sub

To autostart the macro

Sub SaveThis()
Application.DisplayAlerts = False
ThisWorkbook.Save

Application.OnTime Now + TimeValue("00:05:00"), "SaveThis"
End Sub

is the macro

Mvh
Mette

 

[GWteB]

Would recommend to use the AutoRecovery feature: Ribbon > File > Options > Save > tick check box Save AutoRecover information every .. minutes

If you want to keep using the OnTime method, then the scheduled time needs to be canceled before the workbook closes. If this is omitted, then Excel will continue to run this macro, even after the workbook has been closed as long as Excel is active. The workbook will then open automatically in order to run the scheduled macro and this may happen unattended, causing the workbook to stay open until it's closed manually, which is most likely the cause of your issue.

The code below will cancel the last scheduled time when the workbook closes. Code goes in the ThisWorkbook module.

Option Explicit

Private Type TLocals
    ScheduledTime As Double         ' <<< storage for scheduled date & time
    Macro         As String         ' <<< storage for macro to be scheduled
End Type
Private this As TLocals

Private Sub Workbook_Open()
    ReSchedule
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ScheduleCancel
End Sub

Public Sub ReSchedule()
    With this
        .ScheduledTime = VBA.Now + VBA.TimeValue("00:10:00")
        .Macro = "ThisWorkbook.SaveThis"
        Excel.Application.OnTime .ScheduledTime, .Macro
    End With
End Sub

Public Sub ScheduleCancel()
    On Error Resume Next
    Excel.Application.OnTime EarliestTime:=this.ScheduledTime, Procedure:=this.Macro, Schedule:=False
End Sub

Public Sub SaveThis()
    Application.DisplayAlerts = False
    ThisWorkbook.Save
    Application.DisplayAlerts = True
    ReSchedule
End Sub