Sharing Workbooks (Excel 2003)

Topic

New Reply

I am aware of how most of the MVPs feel about sharing workbooks. However, I have 15 users that have to get into various workbooks during month closing for editing and viewing, and I do not know what to recommend to them instead of sharing.
Any ideas?
Thank you, itconc

Reply | Quote

Replies

I don’t how feasible this is but how about BET: Microsoft Excel Track Changes – Merging Shared Workbooks

Reply | Quote

Thanks. However, I need something simple – to my users.
itconc

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Found this product out on the WEB. I don’t use it, don’t know if it will help, but they do have a free demo you can download.

http://badblue.com/helpxls.htm%5B/url%5D

Regards,

tom Duthie

Reply | Quote

If you need users to be able to edit the workbook simultaneously, you could look at the product mentioned by Tom Duthie (I have no experience with it), otherwise I’d go with Steve’s suggestion to make the workbook open as read-only by recommendation. That way, multiple users can view the workbook without much hassle, and someone who needs to edit data can still do that (but only one at a time).

Reply | Quote

Thanks. I think that will be the only answer, although I will test the program mentioned.

Reply | Quote

I recommend making the workbook recommended to be read only unless they are going to edit it (part of the options while saving).

This allows reading it all they want and only requires “edit issues” if more than one needs to update at the same time. If this is a requirement of your database, then I would recommend using something other than excel. Access, for example, works differently and updates the opened file (as opposed to updating the file in memory and rewriting the saved file much later) so is a much better choice for multiple edits in a database.

Steve

Reply | Quote

Thanks. I know Access works better – I am dealing with 150 accountants and Access is like a foreign language to them.
itconc

Reply | Quote

If the user interface is well written, the accountants should not be aware of what program they are using. All you need is one good access programmer to create the application…

Steve

Reply | Quote

I agree. However, these accountants work with many spreadsheets and it would be impossible to use Access for each one.

Reply | Quote

They don’t have to use it for all of them, only those that require that multiple people must be edit it any time. If the editing can be done at different times, there is no issue with using excel. They can view it and work with it at any time no matter who has it open – it will be “read only”. Only the person editing it at that particular time needs to have the ability to edit and save it.

They all must be courteous and not leave the editing workbook open so that others can not edit it however and others must be patient enough to allow one to finish editing it, before they get to make their changes.

Steve

Reply | Quote

If I had considerate users I would be so happy. All of our spreadsheets are treated in a mutli-user fashion.
Thanks for the input.

Reply | Quote

Unfortunately the lack of consideration is generally a problem with anything that is shared in any manner….

Steve

Reply | Quote

[indent]

---

All of our spreadsheets are treated in a mutli-user fashion.

---

[/indent]Wow. I sincerely hope you have an excellent backup system in place! (and proper procedures to check data and model validity).

Reply | Quote

We have an ecellent backup system. However, our control systems leave a lot to be desired. We are working on that.

Reply | Quote

I am interested in this question as well……I have an Excel file that 1 person will work in. Others will want to have read-only access to it on their computers and will not be able to do any editing etc…….can the read-only people see data in real time or will they have to wait til the editing is done and the file re-saved? Or, is there some sort of ‘refresh’ mechanism (eg: F9 to re-calculate) that will allow a reader-only to refresh/reload their ‘view’ of the file and see any editing that has occured?

Reply | Quote

They will have to re-open the file to see the updates.

Reply | Quote

Thank you for that….if they had originally opened it in their browser, would refreshing the browser bring the updates forward or do they have to actually close out of the Excel file and re-open it ….? Do you know of any 3rd-party add-on for Excel that would be a shortcut way of doing this? Thanks…

Reply | Quote

As far as I know, an excel file opened in a browser window is stil handled by Excel in the background, so I expect refreshing will not work.

Try if adding this code to a normal module helps:

Code:

Option Explicit

Sub Auto_Open()
	If ThisWorkbook.ReadOnly Then
		'Reopen me in 4 seconds
		Application.OnTime Now() + TimeValue("00:00:04"), "CloseMe"
	End If
End Sub

Sub CloseMe()
	'Schedule dummy macro so file opens again
	Application.OnTime Now + TimeValue("00:00:00"), "OpenMe"
	ThisWorkbook.Close False
End Sub

Sub OpenMe()
	'No need to have this sub do anything
End Sub

Reply | Quote