Excel VBA Auto save error

Question

To someone who can help me.

I have a network excel file lifting data from some third party software and then saving the data so that other excel files can in turn lift the data for graphs, etc. The third party lift works perfectly, so I don't have any issues with that.

What I have is the excel file running a VBA countdown script to automatically save every 5 minutes so that new data is visible to all (I'll post the vba code I have at the bottom of the question).

Unfortunately, what's happening is that it will work for some period of time, but every now and then it throws up an error message, and when it does, it waits for someone to manually select "OK" before proceeding, essentially pausing the update.

The computer running this is not in an easily accessible location by necessity, meaning most people on site can't just pop in and select "OK" to get it running again.

The error message is: "Someone else is working in '[path\file.xlsm]' right now. Please try again later.

I suspect the error is happening whenever it tries to save at exactly the same time one of the other aforementioned excel files tries to update.

What I want to happen, instead of throwing up the error message, to just ignore the issue and restart the countdown. A 5 minute delay in the data is not an issue.

I tried including "On Error Resume Next", but it didn't work.

Running latest version of office 365, our company keeps it up to date.

See my code now, N.B. Start_Counter() is run automatically when the file is loaded and Stop_Counter() is run automatically when the file is closed.

Public RunWhen As Double

Public Const cRunIntervalMinutes = 5 ' five minute counter

Public Const cRunWhat = "Save_File"  ' Save file macro

Sub Start_Counter()

    'Start counter as soon as file is opened, but only if opened read/write.

    'Every time the timer finishes, the file will save.

    If ActiveWorkbook.ReadOnly Then

    Else

        RunWhen = Now + TimeSerial(0, cRunIntervalMinutes, 0)

        Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _

            Schedule:=True

    End If

    'So Excel won't ask to save when I close the file

    ThisWorkbook.Saved = True

End Sub

Sub Save_File()

'

' Save File, then restart timer.

'

    On Error Resume Next    'On Error ignore the error and go to the next command

    ThisWorkbook.Save

    On Error GoTo 0      'Cancels the Resume Next.

    RunWhen = Now + TimeSerial(0, cRunIntervalMinutes, 0)

    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _

        Schedule:=True

'

End Sub

Sub Stop_Counter()

'Cancel timer on file close.

On Error Resume Next

    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _

        Schedule:=False

End Sub

Answer 1

Well, at least it didn't take long.

I came in this morning and it had happened again.

Any other ideas?

Answer 2

Thanks Doug,

Giving it a go. So far it's still doing it's normal job.

Unfortunately the issue is very sporadic. Sometimes it happens 3 to 4 times per day, sometimes it'll be fine for 3 days.

I won't be sure it's working until I've gone for a week or so with no issues.

If it works I'll come back and hit the "yes" on the "Did this solve your problem".

Answer 3

Try this for the error handler

ErrHandler:

If Instr(Err.Description, "Someone else is working in") > 0  Then 

   Err.Clear

   GoTo StartAgain

ElseIf Err.Number>0 Then

   MsgBox Err.Number & " - " & Err.Description

   Exit Sub

In the following Technet Forum thread

https://social.technet.microsoft.com/Forums/Azure/en-US/824030f2-f20a-4ed9-b368-b052e8a26b30/excel-file-save-error-someone-else-is-working-in-ltfilegt-right-now-please-try-again-later

it is mentioned that a similar issue was caused by the WebRoot virus protection on the client device.

There's also a suggested modification to the Registry in the following thread

https://social.technet.microsoft.com/Forums/en-US/a902f387-f74e-4186-b4aa-9388d01fca15/someone-else-is-working-in-file-path-right-now-please-try-again-later

Answer 4

No, there's no number.

Just what I put above:

"Someone else is working in '[path\file.xlsm]' right now. Please try again later." in a popup box.

Answer 5

Does the Error message have a number?

If it does, you should use something like

Sub Save_File()

'

' Save File, then restart timer.

'

    On Error GoTo ErrHandler    

    ThisWorkbook.Save

StartAgain:

    RunWhen = Now + TimeSerial(0, cRunIntervalMinutes, 0)

    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _

    Schedule:=True

ErrHandler:

If Err.Number = # Then 'the number of the error with the message "Someone else is working in '[path\file.xlsm]'" 

   Err.Clear

   GoTo StartAgain

ElseIf Err.Number>0 Then

   MsgBox Err.Number & " - " & Err.Description

   Exit Sub

End If

End Sub