At work we have a setup where one user opens and updates a spreadsheet whilst several other users have it open for reference as read-only. When the main user changes and saves the workbook those changes aren’t reflected in the open read-only copies. Is there a way to refresh an open, read-only spreadsheet to reflect changes in the files? Currently closing and re-opening the files is the only way that seems to work.

some info here may be of help

http://office.microsoft.com/en-us/sharepoint-server-help/refresh-external-data-in-excel-services-HA010105474.aspx

It may require a sharepoint server though.
Alternatively use google docs which is active live for all users simultaneously.

Thanks Stoner.

As far as I’m aware the file is just stored on a network drive. There are some bits and bobs on the internet involved visual basic that might be worth a look at some point too.

I think the VBA solutions mainly use scheduled closing/reopening to refresh the data. The problem with that though is that between refreshes the data is out of date anyway and if the author hasnt saved the file then the refresh doesnt update with the latest data either.

To be able to maintain absolutely concurrent data I think you need a different set up or an online product.

The simplest way I can think of is to have a VBA macro run in the read only versions which checks the file change date, say every 10 seconds, and then does a ‘hidden’ close and open without the user noticing. NB hidden close and open is pretty simple, set application.screenupdating=false andvthen do the close and open and then set to true. You might need to remember which worksheet / cell they were looking at to get the identical screen position.