File locked by another user - wait and check again

[SnareDrummer]

Hello!
Each member of my team has its own Excel file which uploads data into global file. Works fine until two (or more) members try to upload data at the same time. I've tried to make a 'wait and check again' loop based on checking if the global file is locked or not but it doesn't work as expected
My first try was code that I googled (can't find the sourse, sorry):

Function FileLocked(strFileName As String) As Boolean
   On Error Resume Next
   ' If the file is already opened by another process,
   ' and the specified type of access is not allowed,
   ' the Open operation fails and an error occurs.
   Open strFileName For Binary Access Read Write Lock Read Write As #1
   Close #1
   ' If an error occurs, the document is currently open.
   If Err.Number <> 0 Then
      ' Display the error number and description.
      MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
      FileLocked = True
      Err.Clear
   End If
End Function

The function should return an error when trying to open the file for 'read write' type of access as the file is locked and therefor 'read only'. The problem is that the function never returns any error.
I have also tried to search the folder with the global file for temporary file with name starting with '~$' but I get 'file doesn't exist' message.
I have tried the second solution from this site:
Find out who has Excel file locked for editing - wellsr.com
but does not work either.
In addition, when the file is locked I would expect the pop up window with question what to do next: open as read only, notify the user etc. - hope you know what I'm talking about. But instead I a pop up with a message that the file is protected and a text box to type the password into. I really don't get it as the password is provided by the VBA code and works fine when the global file isn't locked.

Any idea how to make this 'wait and check again' loop? Or how to deal with this 'uploading at the same time' problem other way?

 

[dmt32]

Hi welcome to Forum
If you mean that your Users are opening a master workbook (database) on your network just to "Upload" data from it, then you probably only need to open it in ReadOnly mode which should avoid any multi access conflicts

example

Set wb = Workbooks.Open(Filename, ReadOnly:=True, Password:="mypassword")

The FileLocked Function should only be required where your users need to open the master workbook read/write to enter data.

Hope Helpful

Dave

 

[SnareDrummer]

Hi Dave!
Thanks for your answer. Users are opening a master workbook and upload data from their files into the master file so they need 'read write' access. They don't get data from master file, they feed master file with data.

 

[dmt32]

Hi Dave!
Thanks for your answer. Users are opening a master workbook and upload data from their files into the master file so they need 'read write' access. They don't get data from master file, they feed master file with data.

That was next part of my question but seems to have been omitted from my post

Can you share the code you use to open the master file - I created a similar code that prompts users when there are conflicts.

Dave

 

[SnareDrummer]

Here is my code:

Sub Upload_data()
Access_the_master:
If Not FileLocked("..........Master.xlsx, password:=pass") Then ' If the function returns False, open the document
Workbooks.Open Filename:="..........Master.xlsx", password:=pass
'
'feeding the Master with data
'
Workbooks("..........Master.xlsx").Save
Workbooks("..........Master.xlsx").Close
Else 'file is locked, the function return True
Application.Wait DateAdd("s", 1, Now()) 'wait 1 second
GoTo Access_the_master ' try again
End If
End Sub

I will appreciate if you share your code prompting users about conflicts, maybe there's something that will open my eyes.

 

[dmt32]

Probably simpler if I make some changes to your existing function

Function FileLocked(ByVal strFileName As String) As Boolean
    Dim Response As VbMsgBoxResult
   
CheckFile:
    On Error Resume Next
' If the file is already opened by another process,
' and the specified type of access is not allowed,
' the Open operation fails and an error occurs.
    Open strFileName For Binary Access Read Write Lock Read Write As #1
    Close #1
    FileLocked = CBool(Err.Number <> 0)
    On Error GoTo 0
    If FileLocked Then
'read / write file in use
        Response = MsgBox("File Is Open For Editing By Another User." & Chr(10) & _
        "Do You Want To Try Again?", 37, "File In Use")
        If Response = vbRetry Then GoTo CheckFile
    End If
End Function

Updated Upload_data code

Sub Upload_data()
    Dim MyFile As String
    Dim wb As Workbook
   
    MyFile = "C:\MyFolder\Master.xlsx"
   
' If the function returns False, open the document
    If Not FileLocked(MyFile) Then
        Set wb = Workbooks.Open(FileName:=MyFile, ReadOnly:=False, Password:="pass")
'
'feeding the Master with data
'
'close & save
        wb.Close True
    End If
End Sub

Ensure you enter the correct FilePath for MyFile variable

Updates untested but hopefully will do what you want

Dave

 

[SnareDrummer]

Hi Dave, thank you for your answer. I'm not working this week but as soon as I get to the office I'll test your code and let you know

 

[MCLIFTO8]

I tested the code, it works as expected and makes for a great snippet to put in one's library.

Further reading:

Open statement (VBA)

Office VBA reference topic

learn.microsoft.com

Line Input statement (VBA)

Office VBA reference topic

learn.microsoft.com

@Dave (dmt32),
I know this is a years old post but SnareDrummer never confirmed your answer and if google sent me here, others might also benefit.

 

[dmt32]

I tested the code, it works as expected and makes for a great snippet to put in one's library.

@Dave (dmt32),
I know this is a years old post but SnareDrummer never confirmed your answer and if google sent me here, others might also benefit.

that's both very kind & thoughtful of you - I just modified OPs original code which hopefully, did what was wanted but as say, never confirmed.

I have since this, shared a similar code for another here (who also did not reply) that I created some years ago for an application I created for my daughter which may also find useful

OpenDatabase

Dave