File locked for editing but no lock file

[Darrell]

Maybe not an Excel question per se but this is where the problems are occurring so I'll post it here.

I constantly have issues where I go to open one of my workbooks (on a network) and it comes up with the msgbox stating that "file is locked for editing by 'another user' " or by an actual user who I know isn't even on site or I know they don't have the file open as I go to talk with them about it.

So I got so annoyed with this that I created a procedure to find the hidden lock file and delete it however the issue I'm having today has me stumped. I get the 'locked.. by 'another user' msgbox, but when I run my program I can't find any lock file for the workbook at all.

I even had to go to the inconvenience of manually looking for it and it just doesn't exist.

So where is the workbook getting it's information about being locked from...?

 

[The_Doc_Man]

There is such a thing as a file lock from Windows. It is also possible to open an Access app without an .LDB file if you open it exclusively. The file system takes out a lock on the file before Access or Excel or Word or ... whatever ... sees the targeted app file. This is the lock that prevents the file from being deleted out from under you while your app / utility is busy in a shared folder.

Look at this link for a way to discover your "locker" task.

https://helpcenter.gsx.com/hc/en-us/articles/115015880627-How-to-Identify-which-Windows-Process-is-Locking-a-File-or-Folder

From the Command Prompt you could issue the command OPENFILES /Query which would let you see what files are open at the Windows locking level. That same command has a /Disconnect option to close the file. If you are at the command prompt, use OPENFILES /? to see the options and syntax.

 

[Darrell]

Thanks for that Doc_Man, will look into the link and whether or not I'm even allowed to use it at work.

As for the OPENFILES /Query - I got a 'ERROR: Access is denied.' which I guess means that I would have to run as an Administrator, but then I have no authorisation for that either.

 

[The_Doc_Man]

Yep, admin is required. If that is a work computer, you might need some IT assistance in debugging.

 

[Isaac]

Maybe not an Excel question per se but this is where the problems are occurring so I'll post it here.

I constantly have issues where I go to open one of my workbooks (on a network) and it comes up with the msgbox stating that "file is locked for editing by 'another user' " or by an actual user who I know isn't even on site or I know they don't have the file open as I go to talk with them about it.

So I got so annoyed with this that I created a procedure to find the hidden lock file and delete it however the issue I'm having today has me stumped. I get the 'locked.. by 'another user' msgbox, but when I run my program I can't find any lock file for the workbook at all.

I even had to go to the inconvenience of manually looking for it and it just doesn't exist.

So where is the workbook getting it's information about being locked from...?

is there any chance that you, your app, or someone else or their app, is connected to the excel file (maybe from Access, or wherever, really) in such a way that it locks it?

for example if i link an excel file to an access database, the excel will say 'locked' if I have the "linked excel table" open in access.......or am doing just about anything else with it (one of many reasons i hate linking to excel from access).

but it could be anything non-access, too. if someone connected to the excel file from a software program, ran an ssis package against it, or i don't know....a lot of things like that - theoretically, the result would be, the file could be 'locked' WITHOUT creating the visible "lock" file (starting with a tilde) that you're expecting, which is (I think) usually only created if the file is directly opened by someone in excel, manually.

 

[Darrell]

is there any chance that you, your app, or someone else or their app, is connected to the excel file (maybe from Access, or wherever, really) in such a way that it locks it?

The workbook is pretty much 'stand-alone' and not linked with anything.

I think I've pretty much narrowed it down to people who are using the file in read-only mode while I've had it in write mode. So when I close the workbook the locking file gets deleted, but for some reason there is a snag with it on the read-only file and Excel thinks that 'Another User' has it reserved for editing.

Essentially, if I have the problem now I send out an email to all staff asking them to close any read-only copies and viola, I can now open it in write mode.

 

[The_Doc_Man]

I should point out for clarity's sake that Excel and Access have vastly different locking strategies. Excel is NOT designed to be shared. Access, however, is DEFINITELY designed for sharing. So with Excel, first user wins the lock race. With Access, there is room for more than one user at a time. Therefore, I have to say it is not a surprise that you are seeing this problem.

for some reason there is a snag with it on the read-only file and Excel thinks that 'Another User' has it reserved for editing.

I'm not sure exactly what you mean by that, but did you mean that when you (who held the write lock) close your work, the file acts as though one of the other users suddenly holds the lock?

This is called "lock conversion." If it is what you mean, the rule for Windows file locking is that the competing users take out what are called "interest" locks on a file they want to modify but can't - because they don't own the write lock. When the write lock owner releases the file, the next holder of a "write-interest" lock is granted the write lock because of that recorded interest in modifying the file. If the holder of a "write-interest" lock abandons the file before gaining the lock, the next interest holder becomes next in line, and so on. In essence, Windows LINEARIZES access to the file using the "write-interest" lock in a queue style of waiting list. If I recall correctly, the queue is ordered by priority first and chronology second. So in the case of all user processes running at the same priority, the queue is FIFO with random extraction for abandoned locking cases but ordered insertion. I.e. in formal queue theory, it is an input restricted queue.

 

[Darrell]

I'm not sure exactly what you mean by that, but did you mean that when you (who held the write lock) close your work, the file acts as though one of the other users suddenly holds the lock?

That's ok I wasn't exactly sure either but what you have described is pretty much the behavior I'm seeing.

What I find strange though is that there is no locking file present at all when this happens.

 

[The_Doc_Man]

Not being an expert on Excel locking details, I'll have to speculate that for Excel, the lock file isn't used the same way. I looked up the concept and it appears that Excel 365 supports something called "co-authoring" - and the way it was presented, it implies that other versions do not. Does each of your users run on the same version of Excel? And what version is that?

 

[Darrell]

Hi Doc, yes we are all using the same versions and I also have my workbooks open with a choice between write and read-only so staff will only go into one in write mode by accident, which is no problem as we can tell who it is. We are using 365 yes.

I guess what I need to do now is work out how to have my procedure close all open copies of the file if a locking-file doesn't exist, which already sounds like a headache lol I might just stick with the email to all and wait for them to read it.