Excel File Is Locked Try the Command Again Later
The file is locked. Try the control again later
- nameltrab
-
- #one
Hi all,
I've been getting this error on occasion recently.
- It's a shared certificate
- At that place is usually 10-12 people using the file at the aforementioned fourth dimensionI've read on the Microsoft Assistance site that the issue is that somebody is accessing the file while some other is trying to open up it.
Is there a way, perhaps through VBA, that I tin not allow a user to save while somebody else is opening the certificate? Other suggestions on how to avoid this error?
Thanks!
-
- #2
Re: The file is locked. Try the command again later
You lot could add this function in a VBE module
Role IsFileLocked(filePath As String) As Boolean On Error Resume Side by side Open filePath For Binary Access Read Write Lock Read Write As #1 Close #1 If Err.Number <> 0 Then IsFileLocked = Truthful Application.StatusBar = "Waiting for file to shut" Err.Clear Else IsFileLocked = False Application.StatusBar = "" End If End PartDisplay More
And so something like this in the workbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI Every bit 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 once more subsequently") Cancel = True End If End Sub -
- #three
Re: The file is locked. Try the command once more later
Hi EggCell,
First, sorry it took so long to become back - decorated! The formula looks good - the simply problem is where I need to insert the filename:
fName = "your file proper name" 'file proper name goes hereThe filename changes daily. How would I insert a variable there that would reflect the current filename?
Thanks again for your assist,
Eric
-
- #4
Re: The file is locked. Try the control again later
Hi EggCell,
I've placed the code in the ThisWorkbook module, and identified the filename every bit beneath:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim fName As String fName = ThisWorkbook.Name If IsFileLocked(fName) And so MsgBox ("File is locked" & vbCrLf & "Please try again later") Abolish = True End If Finish SubAt present - the status bar continually says "Waiting for file to close" and I get the "The file is locked. Please attempt again later" bulletin each time I attempt to save the file, despite the file not being shared.
-
- #v
Re: The file is locked. Endeavour the command once again subsequently
Quote
At present - the status bar continually says "Waiting for file to shut" and I get the "The file is locked. Please try once more later" message each time I endeavour to save the file, despite the file not beingness shared.
i. Delete the lines with Application.StatusBar alltogether, they're not so useful.
ii. "The file is locked. Please try again afterwards" Isn't that normal if you test ThisWorkbook (y'all use ThiosWorkbook.Name and run into if it'southward open or non -plain it's open). -
- #6
Re: The file is locked. Endeavour the command once more subsequently
Hullo Wigi,
I idea the code there checks, not if the file is open up, but if the file is locked (while somebody else is saving it, for instance...)
fName = ThisWorkbook.Name If IsFileLocked(fName) ThenIf this is not the case, is at that place another way to word it ?
-
- #7
Re: The file is locked. Try the command once more after
Would information technology be helpful if you test the number of users in the file?
MsgBox UBound(ActiveWorkbook.UserStatus) -
- #8
Re: The file is locked. Try the command once again afterwards
Hmm... I'thousand not sure if that would help - the goal is to prevent somebody trying to access/open the file while it is existence saved to past somebody else. Co-ordinate to the MS KB article, the error message "The file is locked. Try the command again afterwards" is because I am trying to open the shared while at the same fourth dimension, another user has clicked Save. The problem is, that once a user gets this message, trying Save over again in a few minutes does not event in Saving to the original file, it forces a Save Equally and save a second re-create of the file.
-
- #9
Re: The file is locked. Try the control again later
I actually haven't worked with shared workbooks too much, and I wasn't sure how the fileislocked role would piece of work in that case. To Wigi's point, it obviously shows that information technology'due south locked because you take information technology open up. Try this code and see if it doesn't do what yous need. It saves as a "temp" file and then checks to run into if the original file is open. The Do...Loop will await until the file is not locked (presumably afterwards no user is opening it) and and so will relieve it and delete the re-create.
Individual Sub Workbook_BeforeSave(ByVal SaveAsUI Every bit Boolean, Cancel As Boolean) Dim wkbk Every bit String Dim path As String Dim fName As Cord Dim fname2 As Cord 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 Practise Until Not IsFileLocked(fName) If IsFileLocked(fName) And so Application.Expect Now + TimeSerial(0, 0, 0.5) End If Loop ActiveWorkbook.SaveAs fName Impale (fname2) finish: Application.EnableEvents = True Awarding.DisplayAlerts = True Application.ScreenUpdating = True End SubBrandish More
-
- #10
Re: The file is locked. Endeavour the command again later
Then I got a "Sub of Office non defined" for "IsFileLocked"
Practise Until Not IsFileLocked(fName)Foreign, because I didn't go that for the aforementioned argument before ...
-
- #11
Re: The file is locked. Try the command again later
Quote from nameltrab;579198
So I got a "Sub of Function not defined" for "IsFileLocked"
Do Until Non IsFileLocked(fName)Strange, because I didn't get that for the same argument earlier ...
I don't get that message. Exercise you have the function inside the same workbook?
-
- #12
Re: The file is locked. Attempt the command again later
oops - small mistake on my part! Information technology seems to be working ! Hard to know for certain until I get everybody using it ... Thanks for your aid guys! I'll permit yous know how it goes!
Cheers again for your time,
Eric
-
- #thirteen
Re: The file is locked. Try the command once again later
Alter 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
Source: https://www.ozgrid.com/forum/index.php?thread%2F102056-the-file-is-locked-try-the-command-again-later%2F
0 Response to "Excel File Is Locked Try the Command Again Later"
Postar um comentário