Jump to content
Macro Express Forums

Excel file locks


Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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... :rolleyes:

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...