[Webinar] Streamline your web hosting managementRegister Today

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

Excel spreadsheet exclusivity?

I have a user that is unable to make his Excel spreadsheets shared on his network drive.  When done locally, it works fine.  The error that he recieves is 'this book was made exclusive by John Doe on 3/19/10 at 14:15'.  

We are running Server 2k3 and XP workstations.  Can anyone give me some insight to this?
0
comtnbkr
Asked:
comtnbkr
  • 3
  • 2
  • 2
  • +3
1 Solution
 
alexsoftnyCommented:
What is it the version of MS Excel?
0
 
comtnbkrAuthor Commented:
2003 SBE
0
 
alexsoftnyCommented:
Ok you must share the workbook, to do that you need to go to the menu TOOLS and choose the option SHARE WORKBOOK, and then follow the instructions.
0
Get Cisco Certified in IT Security

There’s a high demand for IT security experts and network administrators who can safeguard the data that individuals, corporations, and governments rely on every day. Pursue your B.S. in Network Operations and Security and gain the credentials you need for this high-growth field.

 
patrickabCommented:
comtnbkr,

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

In the Excel 2007 Help files, the item "Use a shared workbook to collaborate" found under "Collaboration - Using Shared workbooks" you will find this:
"Not all features are supported in a shared workbook. If you want to include any of the following features, you should add them before you save the workbook as a shared workbook: merged cells (merged cell: A single cell that is created by combining two or more selected cells. The cell reference for a merged cell is the upper-left cell in the original selected range.), conditional formats (conditional format: A format, such as cell shading or font colour, that Excel automatically applies to cells if a specified condition is true.), data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.), charts, pictures, objects including drawing objects, hyperlinks (hyperlink: Coloured and underlined text or a graphic that you click to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an intranet. Hyperlinks can also go to newsgroups and to Gopher, Telnet, and FTP sites.), scenarios, outlines (outline: Worksheet data in which rows or columns of detail data are grouped so that you can create summary reports. The outline can summarize either an entire worksheet or a selected portion of it.), subtotals, data tables (data table: A range of cells that shows the results of substituting different values in one or more formulas. There are two types of data tables: one-input tables and two-input tables.), PivotTable reports (PivotTable report: An interactive, cross-tabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.), workbook and worksheet protection, and macros. You cannot make changes to these features after you share the workbook."

Patrick
0
 
comtnbkrAuthor Commented:
That is not the answer I wanted to see.  Does anyone else have any ideas? (I'm not discounting your input, Patrick).

Thomas
0
 
patrickabCommented:
Thomas,

>That is not the answer I wanted to see.

I am sure that is not the answer that you wanted to see, but if you ignore the advice then at least I have done my best to warn you of the consequences. I have also given you a workaround so that you can avoid the problem altogether.

Patrick
0
 
comtnbkrAuthor Commented:
I'm not entirely convinced that this is the only way to remedy this...
0
 
WarnseyCommented:
... and me neither but why is the soluition accepted ?
it IS good advice but we have a file here in our business with the same issue and the users are telling me it HAS to be shared
I have many many times advised the user base to avoid at all costs excel sharing - looking for Access or SQL based solutions for concurrent database use, but sometimes the paymaster gets what they want regardless of the consequences
0
 
WarnseyCommented:
I know this topic is closed but I have found a fix for this - in my case at least
The file was being saved as an .xls file
I changed this to save the file as an .xlsm file (macro enabled) - this then allowed the Share Workbook option to remain on and the error did not come up anymore

I look forward to receiving many loud complaints from the users once the file is corrupted :(
0
 
posadminCommented:
I have found this to be an issue with spreasheets shared on a network.  Save the spreadsheet locally and make it a shared spreadsheet.  I find I don't get the error on a local drive.   Then copy it back to the network share and you will no longer get the "this book was made exclusive" error message.
0
 
Varda2Commented:
The last response by posadmin resolved the problem for me (using MS Excel 2007): "Save the spreadsheet locally and make it a shared spreadsheet.  I find I don't get the error on a local drive.   Then copy it back to the network share and you will no longer get the "this book was made exclusive" error message."
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now