My app contains 2 .xls workbooks, the main workbook and the database workbook. Users run the main workbook and do.....stuff, which writes data results to the database workbook.

To guard against errors I have the following code in place in case two users try to submit data to the database workbook at the same time:

Function IsFileLocked(filePath As String) As Boolean
     
    On Error Resume Next
    Open filePath For Binary Access Read Write Lock Read Write As #1
    Close #1
    If Err.Number <> 0 Then
        IsFileLocked = True
        Err.Clear
    Else
        IsFileLocked = False
    End If
    On Error GoTo 0
 
End Function
The above is called from the macro that tries to write data to the database workbook thusly:


Dim path as string
Path = Range("D14").Value

Do Until IsFileLocked(Path) = False
Loop

Workbooks.Open (Path), Password:="temppassword"
Sheets("db").Select
Cells(user, 36).Value = testresult
ActiveWorkbook.Save
ActiveWindow.Close
This works absolutely fine in a test environment, the main workbook will just pause for a few milliseconds when clicking 'Submit' until the database workbook is available. If I open the database workbook manually on another PC then the above code will just sit at an hourglass until I close it on said other PC. No problem.

However, when testing in a larger, slower, environment I've found something very odd.
Very, very rarely (so rarely in fact that it's impossible to replicate), instead of pausing until the database workbook is free instead it gets overwritten by a new, blank workbook with one single test result in cell A1.

And I can't work out why. It might be something to do with a slow network or old, dodgy file server that's causing it to not be able to find the workbook file......

Further testing has discovered that if the database workbook doesn't exist then the main workbook will create a new (blank) one and save it in the original database workbook's location. That I don't get either. Surely, the above code would error in the isfilelocked function and therefore return an = true value and loop?