How to Automatically Close a workbook at a specific time?

[Bozacke]

Problem - I have a workbook, call it workbook1 that gets updated every day by a macro in workbook2. It works great except for one problem, a host of people need read/write access to workbook1. I already have workbook1 as recommended read-only, but occasionally some one will open up workbook1 and forget to close it and hence my macro isn't able to update the file.

My preference would be to 1). have another scheduled task to run at say 6AM and if workbook1 is open, just close it.

If I can't do that I'd like to 2). install an event in Workbook1 that closes it at 6am, but how would I trigger that event.

Because I didn't know how to do either of the 2 options above, what I have done is 3). I've installed a macro that starts the clock ticking when the file is opened and then gives a warning message telling them to close the file after 1 hour and if they don't close it in another 15 minutes it closes the file automatically and doesn't save the changes, but does save there changes as a backup file just in case. But despite this, some of my colleagues aren't happy with this solution, these are the same people who constantly forget to close the workbook.

Any suggestions on how to do either 1 or 2 above?

 

[k209310]

Re: How to Automatically Close a workbook at a specific time

Hello Bozacke

you can call a macro a a specicif time using the OnTime Event. The example below will run mymacro at 3'oclock every day. If you put the code to clos the workbook in there it should work for.

Private Sub Workbook_Open()
    Application.OnTime TimeValue("15:00:00"), "mymacro"
End Sub

Hope this helps

Chris

 

[Bozacke]

Re: How to Automatically Close a workbook at a specific time

Chris,

Thanks for your reply, that works Great!!!

The only problem I have now is if some one opens my file with their Security level set for High or Medium and then the macro won't run and the file could be left open. Any suggestions for a work around that one?

I suppose you can't idot proof everything 100%.

 

[k209310]

Re: How to Automatically Close a workbook at a specific time

No bother, im glad i could help

Unfortunatly tho there is no way of changing the settings a user has on their machine. IE the macro security level stteing. There are ways around this but none of them are very elegant. Try doing a search on the board, I remember reading some good ideas about this topic.

Chris

 

[steve case]

Re: How to Automatically Close a workbook at a specific time

Would making it a shared file help?

If it's shared, multiple people can have it open and make changes.

 

[Bozacke]

Re: How to Automatically Close a workbook at a specific time

StACase,

Thanks for the suggestion, but I actually have to re-write the file when my macro runs so a shared file doesn't work because I'm over writing the file.

I've used k209310 suggestion and I wrote a macro in there that closes them out if the file is open at 5AM and just in case I save whatever they may have done into a backup folder with the file name their NTLogon and the date. This way I can find out who is leaving it open and how often and if they had any critical changes in there it's all backed up.

So far so good.

Thanks for the help from all here!!!

 

[Bozacke]

Re: How to Automatically Close a workbook at a specific time

I think I may have found a way around the problem where the user has their Security settings set at High (this prevents my autoclose macro from running)

Could you have a Private Sub Workbook_Open() set to check the security levels and if they aren't set at Low you have to open it as read-only. But since the Private Sub Workbook_Open() will not run if the security settings are set at High this wouldn't work. But if I make my workbook as read-only and have a password in the macro but it only unprotects the worksheet if the macro is on and the security settings obviously need to be set at Low for that to work.