Announcement

Collapse
No announcement yet.

The file is locked. Try the command again later

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • The file is locked. Try the command again later



    Hi all,

    I've been getting this error on occasion recently.

    - It's a shared document
    - There is usually 10-12 people using the file at the same time

    I've read on the Microsoft Help site that the issue is that somebody is accessing the file while another is trying to open it.

    Is there a way, perhaps through VBA, that I can not allow a user to save while somebody else is opening the document? Other suggestions on how to avoid this error?

    Thanks!

  • #2
    Re: The file is locked. Try the command again later

    You could add this function in a VBE module

    Code:
    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
            Application.StatusBar = "Waiting for file to close"
            Err.Clear
        Else
            IsFileLocked = False
            Application.StatusBar = ""
        End If
    End Function
    And then something like this in the workbook module
    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim fName As String
    fName = "your file name" 'file name goes here
    If IsFileLocked(fName) Then
      MsgBox ("File is locked" & vbCrLf & "Please try again later")
      Cancel = True
    End If
    End Sub

    Comment


    • #3
      Re: The file is locked. Try the command again later

      Hi EggCell,

      First, sorry it took so long to get back - busy! The formula looks good - the only problem is where I need to insert the filename:

      Code:
      fName = "your file name" 'file name goes here
      The filename changes daily. How would I insert a variable there that would reflect the current filename?

      Thanks again for your help,

      Eric

      Comment


      • #4
        Re: The file is locked. Try the command again later

        Hi EggCell,

        I've placed the code in the ThisWorkbook module, and identified the filename as below:

        Code:
        Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Dim fName As String
        fName = ThisWorkbook.Name
        If IsFileLocked(fName) Then
        MsgBox ("File is locked" & vbCrLf & "Please try again later")
        Cancel = True
        End If
        End Sub
        Now - the status bar continually says "Waiting for file to close" and I get the "The file is locked. Please try again later" message each time I try to save the file, despite the file not being shared.

        Comment


        • #5
          Re: The file is locked. Try the command again later

          Now - the status bar continually says "Waiting for file to close" and I get the "The file is locked. Please try again later" message each time I try to save the file, despite the file not being shared.
          1. Delete the lines with Application.StatusBar alltogether, they're not so useful.
          2. "The file is locked. Please try again later" Isn't that normal if you test ThisWorkbook (you use ThiosWorkbook.Name and see if it's open or not -obviously it's open).
          Regards,

          Wigi

          Excel MVP 2011-2014

          For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

          -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

          Comment


          • #6
            Re: The file is locked. Try the command again later

            Hi Wigi,

            I thought the code there checks, not if the file is open, but if the file is locked (while somebody else is saving it, for example...)

            Code:
            fName = ThisWorkbook.Name 
               If IsFileLocked(fName) Then
            If this is not the case, is there another way to word it ?

            Comment


            • #7
              Re: The file is locked. Try the command again later

              Would it be helpful if you test the number of users in the file?

              Code:
              MsgBox UBound(ActiveWorkbook.UserStatus)
              Regards,

              Wigi

              Excel MVP 2011-2014

              For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

              -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

              Comment


              • #8
                Re: The file is locked. Try the command again later

                Hmm... I'm not sure if that would help - the goal is to prevent somebody trying to access/open the file while it is being saved to by somebody else. According to the MS KB article, the error message "The file is locked. Try the command again later" is because I am trying to open the shared while at the same time, another user has clicked Save. The problem is, that once a user gets this message, trying Save again in a few minutes does not result in Saving to the original file, it forces a Save As and save a second copy of the file.

                Comment


                • #9
                  Re: The file is locked. Try the command again later

                  I really haven't worked with shared workbooks too much, and I wasn't sure how the fileislocked function would work in that case. To Wigi's point, it obviously shows that it's locked because you have it open. Try this code and see if it doesn't do what you need. It saves as a "temp" file and then checks to see if the original file is open. The Do...Loop will wait until the file is not locked (presumably after no user is opening it) and then will save it and delete the copy.

                  Code:
                  Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
                  Dim wkbk As String
                  Dim path As String
                  Dim fName As String
                  Dim fname2 As String
                  wkbk = ThisWorkbook.Name
                  path = ThisWorkbook.path
                  Application.EnableEvents = False
                  Application.DisplayAlerts = False
                  Application.ScreenUpdating = False
                  fname2 = path & "\temp.xls"
                  fName = path & "\" & wkbk
                  ActiveWorkbook.SaveAs fname2
                  Do Until Not IsFileLocked(fName)
                    If IsFileLocked(fName) Then
                      Application.Wait Now + TimeSerial(0, 0, 0.5)
                    End If
                  Loop
                      
                  ActiveWorkbook.SaveAs fName
                  Kill (fname2)
                  finish:
                  Application.EnableEvents = True
                  Application.DisplayAlerts = True
                  Application.ScreenUpdating = True
                  End Sub

                  Comment


                  • #10
                    Re: The file is locked. Try the command again later

                    So I got a "Sub of Function not defined" for "IsFileLocked"

                    Code:
                        Do Until Not IsFileLocked(fName)
                    Strange, because I didn't get that for the same statement earlier ...

                    Comment


                    • #11
                      Re: The file is locked. Try the command again later

                      Originally posted by nameltrab View Post
                      So I got a "Sub of Function not defined" for "IsFileLocked"

                      Code:
                          Do Until Not IsFileLocked(fName)
                      Strange, because I didn't get that for the same statement earlier ...
                      I don't get that message. Do you have the function inside the same workbook?
                      Regards,

                      Wigi

                      Excel MVP 2011-2014

                      For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                      -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                      Comment


                      • #12
                        Re: The file is locked. Try the command again later

                        oops - small mistake on my part! It seems to be working ! Hard to know for sure until I get everybody using it ... Thanks for your help guys! I'll let you know how it goes!

                        Thanks again for your time,

                        Eric

                        Comment


                        • #13


                          Re: The file is locked. Try the command again later

                          Change the file name. or Copy the file in your desktop --> rename it --> replace the shared file with this new renamed one This worked for me. this is more a network issue or combination of network or file

                          Comment

                          Working...
                          X