Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

We have an Excel file that is on our network that one person usually keeps open and makes changes to. Other users keep the file open, in read-only mode.

Occasionally, someone other than the first person who is in charge of editing the excel file will open that file and somehow take over write functionality of that file causing the person who is supposed to make edits unable to save those changes.

This has been occuring for quite some time and I can't seem to find a fix for it.

Any ideas what's going on?

Office 2010
Windows 7 Pro x64

share|improve this question
    
Are you saying that the second person opens the file while the first person has it open and takes write access away from the first person? Or just that a second person sometimes gets to work early and opens the file before the first person has a chance? –  Scott Dec 19 '12 at 14:06

3 Answers 3

Here’s a possible fix: From the “Save As” dialog, select “Tools” –> “General Options”, select “Read-only recommended”, and save.

    “Save As” dialog     “General Options” dialog, with “Read-only recommended” marked

Then, any time anybody opens the file, they will get this:

“Open as read-only?” dialog

Teach them to click on “Yes” except for the one person who is supposed to edit the file (or his alternate, in case of absence).

share|improve this answer
    
I did that option without password. Hopefully it works. Thanks for the tip –  Markaway Dec 19 '12 at 14:51

I have chosen another approach for the next situation. There is an Excel database, let say DataBase.xls and an Excel template Programs.xlt, both are not shared and resides on a directory of a network server/disk. Each user uses a copy of the programs template, so Programs1.xls for the first user and Programsn.xls for the n-th user. The rules are: . many concurrent reads at the same time are possible . only 1 write is possible without concurrent reads (in order to avoid reads of "half" updated records) . the read and write actions are standardised and kept as short as possible

The solution I have chosen is a Referee/Umpire/Arbitor/Arbitrator - I called it Referee.xls. It is a shared excel file and resides in the same above mentioned directory and is very small : it only consists of 2 fields - a read counter nRead and a write counter nWrite. Every user who wants access to the DataBase sends the referee a read or write request.

In case of a read request, the referee checks/waits until there are no write actions (anymore) (nWrite=0) and then grant a read for that user by increasing the nRead counter by 1. After a read action is completed, the nRead counter is decreased by 1.

In case of a write request, the referee checks/waits until there are no more write actions active (nWrite=0), after that the Referee checks/waits till all read actions are done (nRead=0) and then grant the write request and set the nWrite counter to 1. After the write action is done the write counter nWrite is set to 0. After that the referee can grant new read and/or write requests. The waits are implemented with the "Sleep" function in Lib "Kernel32" with steps of 1 second (1000 milliseconds). The steps are also counted and after 6 steps/seconds a read request is timed out, a write request is timed out after 10 steps/seconds, but this heavily depends on many factors and should be found in practice.

This all works very nice in an Excel 2003, Windows XP environment with maximum 14 concurrent users. These users are managers working in different places spread over an area of 5000 square kilometers and use the tool every day for updating Medical Competence Information, Personal Information, Appointments etc. for their employees (avg 15 employees per manager)

share|improve this answer

The only way I can think this is happening is either

  • The Allow Users to Edit ranges option configured (see ribbon called Review -> Changes)
  • Person 1 doesn't always have the file open or, maybe if the OS goes in standby the lock on Excel is released.
share|improve this answer
    
The person keeps the file open. I will check if the OS goes into standby as that's the only thing I can think of that would cause this as he occasionally leaves his desk for 10-15 minutes at a time. –  Markaway Dec 19 '12 at 13:08
    
Computer is not set to go into sleep mode or screen saver. The Allow Users to Edit ranges is not configured. –  Markaway Dec 19 '12 at 13:15
    
Where is the file saved (on a network share on his PC (which is shared), and do you share log in details? It may be worth while ensuring that he is in his own group in AD and that the file is explicit write access to him/her only. –  Dave Dec 19 '12 at 13:18
    
The file is on a DroboPro attached to a server (2008 R2) via iSCSI. We have another server with a DroboPro. We use DFS for namespace/replication. I can't strictly give him write access only as other users may need to write to it when he is not available. –  Markaway Dec 19 '12 at 13:23
    
Seems the lock disappears 5-10 seconds after the user saves the file. –  Markaway Dec 19 '12 at 13:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.