Excel File Is Locked Try Again Later
The file is locked. Try the command again later
- nameltrab
-
- #ane
Hi all,
I've been getting this error on occasion recently.
- It'southward a shared document
- At that place is usually 10-12 people using the file at the same timeI've read on the Microsoft Help site that the issue is that somebody is accessing the file while some other is trying to open up it.
Is in that location a way, possibly through VBA, that I can not permit a user to salve while somebody else is opening the certificate? Other suggestions on how to avoid this error?
Thanks!
-
- #two
Re: The file is locked. Try the command again later
You could add this function in a VBE module
Part IsFileLocked(filePath Equally String) As Boolean On Mistake Resume Next Open filePath For Binary Access Read Write Lock Read Write As #ane Close #1 If Err.Number <> 0 So IsFileLocked = True Application.StatusBar = "Waiting for file to close" Err.Articulate Else IsFileLocked = Faux Application.StatusBar = "" End If End FunctionDisplay More
And then something similar this in the workbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim fName As String fName = "your file name" 'file name goes hither If IsFileLocked(fName) Then MsgBox ("File is locked" & vbCrLf & "Please endeavor again afterward") Cancel = True End If End Sub -
- #three
Re: The file is locked. Try the control once more later
Hi EggCell,
First, lamentable it took so long to become dorsum - decorated! The formula looks skilful - the only problem is where I need to insert the filename:
fName = "your file name" 'file name goes hereThe filename changes daily. How would I insert a variable there that would reflect the current filename?
Thanks over again for your help,
Eric
-
- #4
Re: The file is locked. Try the command once more afterwards
Howdy EggCell,
I've placed the lawmaking in the ThisWorkbook module, and identified the filename as beneath:
Individual Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim fName Every bit Cord fName = ThisWorkbook.Name If IsFileLocked(fName) And then MsgBox ("File is locked" & vbCrLf & "Please try over again afterward") Abolish = True End If End SubNow - the status bar continually says "Waiting for file to close" and I go the "The file is locked. Please try once more later" bulletin each time I try to save the file, despite the file non beingness shared.
-
- #5
Re: The file is locked. Try the command once again later
Quote
Now - the status bar continually says "Waiting for file to close" and I become the "The file is locked. Please endeavor again later" bulletin each time I try to save the file, despite the file non being shared.
1. Delete the lines with Application.StatusBar alltogether, they're not so useful.
2. "The file is locked. Please endeavour over again later" Isn't that normal if yous test ThisWorkbook (y'all use ThiosWorkbook.Name and run across if it'due south open or not -plain it's open). -
- #vi
Re: The file is locked. Try the command again later
Hi Wigi,
I thought the lawmaking there checks, non if the file is open, simply if the file is locked (while somebody else is saving information technology, for example...)
fName = ThisWorkbook.Proper name If IsFileLocked(fName) And soIf this is non the case, is in that location another fashion to word it ?
-
- #7
Re: The file is locked. Try the command again later
Would information technology be helpful if yous test the number of users in the file?
MsgBox UBound(ActiveWorkbook.UserStatus) -
- #8
Re: The file is locked. Try the control over again after
Hmm... I'm not certain if that would assist - the goal is to prevent somebody trying to admission/open the file while information technology is being saved to by somebody else. Co-ordinate to the MS KB article, the fault bulletin "The file is locked. Try the command again later" is considering I am trying to open up the shared while at the aforementioned fourth dimension, another user has clicked Save. The problem is, that once a user gets this bulletin, trying Save again in a few minutes does not upshot in Saving to the original file, it forces a Salvage Every bit and relieve a 2nd copy of the file.
-
- #9
Re: The file is locked. Endeavor the command again afterwards
I really oasis't worked with shared workbooks too much, and I wasn't sure how the fileislocked function would piece of work in that case. To Wigi'southward point, information technology patently shows that it'south locked because you have information technology open. Endeavour this code and run into if it doesn't do what you need. Information technology saves as a "temp" file and then checks to see if the original file is open. The Do...Loop volition wait until the file is not locked (presumably after no user is opening information technology) and then will save information technology and delete the copy.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI Equally Boolean, Cancel As Boolean) Dim wkbk As String Dim path As Cord Dim fName Every bit String Dim fname2 As String wkbk = ThisWorkbook.Name path = ThisWorkbook.path Application.EnableEvents = Faux 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 Awarding.ScreenUpdating = True End SubDisplay More
-
- #10
Re: The file is locked. Try the control again afterward
And so I got a "Sub of Office not defined" for "IsFileLocked"
Do Until Not IsFileLocked(fName)Foreign, because I didn't get that for the same statement before ...
-
- #xi
Re: The file is locked. Try the control again later
Quote from nameltrab;579198
And then I got a "Sub of Part non defined" for "IsFileLocked"
Practise Until Non IsFileLocked(fName)Foreign, considering I didn't get that for the same statement earlier ...
I don't get that bulletin. Do you have the function within the same workbook?
-
- #12
Re: The file is locked. Try the command again later on
oops - pocket-size 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 let y'all know how it goes!
Thanks again for your time,
Eric
-
- #13
Re: The file is locked. Attempt the control once again after
Modify the file proper noun. 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://forum.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 Again Later"
Post a Comment