File locked by another user - wait and check again

SnareDrummer

New Member
Joined
Jun 26, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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):
VBA Code:
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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,371
Office Version
  1. 2019
Platform
  1. Windows
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
Rich (BB code):
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
 
Last edited:

SnareDrummer

New Member
Joined
Jun 26, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,371
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Jun 26, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,371
Office Version
  1. 2019
Platform
  1. Windows
Probably simpler if I make some changes to your existing function

VBA Code:
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

VBA 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

New Member
Joined
Jun 26, 2020
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,454
Messages
5,596,228
Members
414,047
Latest member
debbos

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top