Excel file locks

[Cory]

Excel locks file when open or even if accessing as read-only. Does anyone know of a way to get Excel not to do this? MEP will not run an ASCII File Process if the user has it open and usually it's only because they're looking at it and forgot to close it out. Also I have source data files that are created with MEP and are only referenced by Excel but I can't update the files if a user has them open. Short of having a macro sit around and wait all day I can't seem to think of an elegant work around either.

 

Thanks in advance.

[paul]

Excel locks file when open or even if accessing as read-only. Does anyone know of a way to get Excel not to do this? MEP will not run an ASCII File Process if the user has it open and usually it's only because they're looking at it and forgot to close it out. Also I have source data files that are created with MEP and are only referenced by Excel but I can't update the files if a user has them open. Short of having a macro sit around and wait all day I can't seem to think of an elegant work around either.

There are utilities which, when you right-click on a file and select the relevant option, will tell you who is locking the file and offer you the choice of unlocking it.

I use LockHunter x64 and I was able to unlock a text file currently open in Excel. A macro I wrote to read this text file failed to work before I unlocked the file, then worked fine after. And Excel didn't seem to mind either, happily writing back to the file on request.

 

The trouble is there's no command line capability, so it may not be what you're looking for.

 

LockHunter

[terrypin]

Excel locks file when open or even if accessing as read-only. Does anyone know of a way to get Excel not to do this? MEP will not run an ASCII File Process if the user has it open and usually it's only because they're looking at it and forgot to close it out. Also I have source data files that are created with MEP and are only referenced by Excel but I can't update the files if a user has them open. Short of having a macro sit around and wait all day I can't seem to think of an elegant work around either.

 

Thanks in advance.

 

The free program Unlocker should let you do what you want, downloadable from here.

 

As well as being accessible via the r-click menu, it can be run directly. In my case, launching

C:\Program Files\Unlocker\Unlocker.exe

displayed a dialog allowing browsing for the file requiring unlocking.

 

Better, using ME Pro script to enter this

"C:\Program Files\Unlocker\Unlocker.exe" "C:\Docs\TestUnlockerCory.csv"

into the XP Pro Run box took me straight to a dialog targeting the file, needing only a click on 'Unlock' to finish it.

 

Using a CSV file open in Excel, my test macro running an ASCII File process immediately gave the gave the error message "The file is not the correct format." (Puzzling; I'd have expected something like "The file is locked because it's already open.") But after using Unlocker it then ran OK.

 

--

Terry, East Grinstead, UK

[paul]

The free program Unlocker should let you do what you want, downloadable from here.

This program won't run in x64.

[paul]

Excel locks file when open or even if accessing as read-only. Does anyone know of a way to get Excel not to do this? MEP will not run an ASCII File Process if the user has it open and usually it's only because they're looking at it and forgot to close it out. Also I have source data files that are created with MEP and are only referenced by Excel but I can't update the files if a user has them open. Short of having a macro sit around and wait all day I can't seem to think of an elegant work around either.

The following will work; my examples use d:\MyDir\MyFile.txt as the file whose Excel handle you want to close:

- Download the Handle utility from SysInternals

- run handle from your macro to extract the data you need to close the handle:

handle d:\MyDir\MyFile.txt >d:\temp\xx.txt

This will store into d:\temp\xx.txt data like:

Handle v3.42

Copyright © 1997-2008 Mark Russinovich

Sysinternals - www.sysinternals.com

 

EXCEL.EXE pid: 6016 308: D:\MyDir\MyFile.txt

which you can parse to extract the two numbers you need - the pid (6016) and the handle (308)

Note: Even better, use this command:

handle d:\MyDir\MyFile.txt | find /i "excel.exe" >d:\temp\xx.txt

which will return

EXCEL.EXE pid: 6016 308: D:\MyDir\MyFile.txt

- run handle again from your macro to close the handle you just found:

handle -c 308 -p 6016 -y

This will close the handle without any messages; you can now access the file from ME

[Cory]

I was really hoping for a way to command Excel to open without a lock but this might be the only way to work around this problem. And I like the SysInternals and would rather have a command line solution than something GUI so this is an excellent suggestion. Better than waiting around all day for the user to close the file.

 

Now my only problem is whether I'll confuse Excel by changing the file contents. I suppose I could run some experiments to see if there are any adverse effects.

 

Thanks again for the suggestions

[paul]

Now my only problem is whether I'll confuse Excel by changing the file contents. I suppose I could run some experiments to see if there are any adverse effects.

I had a quick play, and Excel happily saved data back to the text file (of course the file was then locked again; and you're not going to be able to control your users to the extent that they won't be saving; but there's no reason why, any time you want to access the file, you don't run that handle command using the find syntax because it'll return nothing if the file isn't locked by Excel).

 

Of course, as they say everywhere these days, no guarantees...

[Cory]

In this case the file is an external reference so I'm hoping that the next time Excel recalculates the changes will be apparent. I'll experiment and report back what I find.