Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 990
  • Last Modified:

Shared Excel File is Disappearing when saving ?

We have a shared file hosted on the server that has disappeared on us twice.

This is the steps that happened today:

1. Both users have the file open.

2. First user makes changes.

3. Second user hits save to see changes, but changes are not seen.

4. Second user closes file and tries to re-open it but finds that the file is missing.

At this point luckily they stopped and got me and I was able to save the file by doing a "save as" to the desktop of the first user and then moving the file back to its original location. My question is what could be causing this short of someone deleting or moving the original file which all users say they did not do?

Thank you.
0
Nadams929
Asked:
Nadams929
1 Solution
 
MWGainesJRCommented:
Sharing an excel file is a bad idea, and this is exactly why.   I think you should consider giving each user there own instance of the file and write back to the master.
0
 
patrickabCommented:
Nadams929,

Excel does not handle 'Shared' workbooks reliably. They are often corrupted at random and apparently without cause. Clearly there is a cause but it has not yet been isolated and fixed by MS. There is nothing you can do to avoid shared workbooks corrupting. The ONLY thing that can be done is to make them single user.

To help you with the single user file system try adopting this approach instead. It does work rather well so it's worth doing. First make the file(s) single user. Second edit everyone's user profile in Excel - Tools/Options/General/UserName and in there put the person's name and telephone extension number. Then whenever you attempt to open a file that someone else is using, a dialogue box will pop-up telling you who is the current user - and it tells you their telephone extension number! The new user can opt to either call the current user or open the file as read-only.

I realise it seems odd that MS would have an unreliable feature in Excel but I'm afraid that's the way it is. If you must have simultaneous access then it would be wise to use a proper database.

For Office 2007:  Office Button > Excel Options >Popular tab >User name

Patrick
0
 
Patrick MatthewsCommented:
It is only a small exagerration to say that there are only two kinds of shared workbooks: those that havebecome corrupted, and those that soon will be :)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
p912sCommented:
Agree with everything above about shared workbooks. They have been a problem since their inception...

But to answer your question as to why your file is deleted; it's because Excel deletes the file as part of its normal operation in saving an existing file. So when an error occurs during a save your original file will disappear.


Description of the way that Excel saves files
http://support.microsoft.com/kb/814068/

When Excel saves a file, Excel follow these steps:

   1. Excel creates a randomly named temporary file (for example, Cedd4100 with no file name extension) in the destination folder that you specified in the Save As dialog box. The whole workbook is written to the temporary file.
   2. If changes are being saved to an existing file, Excel deletes the original file.
   3. Excel renames the temporary file. Excel gives the temporary file the file name that you specified (such as Book1.xls) in the Save As dialog box.
0
 
Nadams929Author Commented:
Problem not solved but explanation was acceptable. Thank you.
0
 
patrickabCommented:
Nadams929,

Thanks for the points. On the matter of grade, if an answer is correct, even if it is not what you wanted as an answer you should in future be awarding an A grade.

The problem of 'solving' shared Excel files can only be provided by MS who have either decided to do nothing about it or cannot find a way to solve the problem. There is no solution other than to use Excel workbooks as single-user files.

Patrick
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now