Close an Excel workbook that is open by another user

[supanoods]

Hi all,

I have 2 spreadsheets on a network. One is a master data file, and the other is like a "viewer". The viewer is refreshed every night via VBA code from the master workbook. The code simply opens the viewer, copies data from master to viewer, saves both and closes both. The viewer is used by many users.

The problem I now have is that the shift pattern of the users has changed to 24/7, therefore there is no definite downtime where I can schedule the refresh. Obviously the refresh won’t work while the viewer file is open.

I have a check in place that tells me who has the file open, but I need a way to terminate all open instances of the shared file or force all users to exit (ie timed usage etc).

Can anyone help or point me in the right direction. All advice is greatly appreciated!

Thanx

Cheers, Supanoods B-)
"If it aint broke - dont fix it!

 

[JerryKlmns]

How about a timer that checks a specific time span and the user name, in order to quit or not quit? You just have to determine that time

 

[combo]

Depending on the purpose and data in the workbook, you could consider save copy as web page, adjust publishing options, use web browser as file viewer. There is no problem in refreshing file in this case.

combo

 

[supanoods]

Thanks guys for your input.

I have written some code since this post that closes the workbook after 30 secs of idle time. So it could be used to catch the users that leave the file open and go home for the evening etc.

But this wont help if the viewer is being used at the time I schedule a refresh.

combo - I would love to have a web based output, but I use a drop down list to chose the info the user wants to see (its a bit like a phonebook, but with more infor about staff - phone numbers, addresses, etc etc). Do you have any links to, or info on web based outputs from Excel???

Cheers, Supanoods B-)
"If it aint broke - dont fix it!

 

[combo]

You can use 'save as' or 'save as web page' options and next configure options. The interaction forces excel to use web components, but they seem to be rather useless in this case.
Maybe create simple page from scratch if the rules are fixed?

combo

 

[combo]

If it makes sense, you can query external data in the viewer or from the viewer workbook. You can set refreshing frequency or update 'on open'. Next this dataset could be processed.

combo

 

[supanoods]

Thanks combo - i am going to look into it!!

Cheers, Supanoods B-)
"If it aint broke - dont fix it!

 

[Bong]

It seems to me that it doesn't really matter if the viewer is refreshed until and unless it is opened. So you could put the refresh in the viewer's module (either to always refresh or only if it's a certain time window).

_________________
Bob Rashkin

 

[supanoods]

Hi Bong,

The master data file is maintained by a small team (8 users), but the viewer has the potential to be used by 100+ users throughout the day. Refreashing everytime would be too much, plus there is an very high chance that more than one instance would be open at once.

Also: Changes to the data are minor therefore a 24hr refresh is acceptable.

The more and more I look into this, I am leaning towards developing an internet browser based solution. Sad;y I am not strictly "IT" therefore limited with what i am allowed to develop and implement.

Cheers, Supanoods B-)
"If it aint broke - dont fix it!

 

[supanoods]

Hi all!

Just to let you know that I came up with kinda work around. I decided to go with using the "OnTime" function.

I added this code to the shared workbook Autpen macro:

Application.OnTime TimeValue("00:30:00"), "Auto_Close"

The Auto_Close macro (which includes a close command) closes the workbook at half past midnight every night - and it applies to all open instances!

Thanks again for all your help and advice

Cheers, Supanoods B-)
"If it aint broke - dont fix it!