Excel - How do I auto update spreadsheet every 5 mins

[beanxx]

I have a spreadsheet which holds roster information that is regularly updated by an administrator. This same spreadsheet sits on a central PC (on the same network) with read only priviledges, and I would like this to automatically update to pick up any changes made by the administrator say every 5 minutes. I guess some form of VBI macro would satisfy this request but can anyone explain exactly how I would do this? Thanks

 

[DPlank]

Depends on what the admin is updating. If it's a database, you need to automate a database query.

If it's another spreadsheet, you just need a time-delayed Calculate statement in the VB.

Which route are we using here?

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[beanxx]

The admin is simply updating exactly the same spreadhseet which sits on a network drive. It's just that the admin has the priviledges to make changes, whereas to the other users this spreadsheet is always open on a central pc. The intention being that this acts almost like a wallpaper which auto updates without having to be re-opened...

 

[xlbo]

If it is the same spreadsheet and the calculation is not set to manual then any changes will be reflected instantly anyway....I am confused

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[beanxx]

To explain better....an excel spreadsheet is opened from a common drive location and sits open on a PC in read only mode. Editing of this spreadsheet is then carried out by a second user by opening the same spreadsheet on a different PC. Once this spreadsheet has been edited I would like these changes to be reflected in the read only version without the user having to re-open the report. In other words every 5 minutes or so I would like the read only version to automatically purge the location the report is stored on and auto refresh any changes. Thanks.

 

[xlbo]

Within the same spreadsheet this is not possible. You have 2 options

1: use a "control" spreadsheet and a "display" spreadsheet with the "display" spreadsheet having linked formulae to the "control" spreadsheet - this will update in real time

2: control everything from 1 spreadsheet and use code to open & close the versions of the workbook - there are ways of doing this so no-one would know except the person controlling the updates

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[beanxx]

Thanks Geoff, I can see how Option 1 would work quite easily although the added complication is that sometimes comments are added to individual cells and I guess these wouldn't be reflected in the "display" sheet? Option 2 may be the best option therefore but I wound't know how I could control the opening of the "display" sheet on a second PC.....short of a VNC session or something like..

 

[xlbo]

sorry - brain strain this morning - missed that it would need to be on a different computer - in which case, the control would have to come from there - you could have an "update" button on the application that closes and then re-opens the read-only file - this could be attached to a toolbar or as a menu option in excel - again this would require code.....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[beanxx]

Excellent idea that would do it. If in the "display" version I simply included an "update" Command Button which then triggered a very simple script to close and re-open the report that would do it. Does that suggestion warrant you a star Geoff?

 

[xlbo]

nope - not at all but post a question about creating a menu option / commandbar option to open and close the current workbook and no doubt you'll be giving somemone a star for the code they provide...never know - might even be me !!

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question