Close file automatically

Is it possible to set up an Excel workbook to timeout and close automatically
after a given amount of time, say 15 minutes? Maybe via a macro that times
out and closes the workbook? Or perhaps a macro from another workbook that
can close it down?

The problem we are having in the Company is that one user will open an Excel
workbook from our network and forget to close it. Then when someone else
tries to open it and make modifications they get the "File in Use" dialog box
which says "filename.xls is locked for editing." and the only buttons
available are "Read Only", "Notify", or "Cancel".

You could use Ontime together with worksheet change event code to monitor
it.


There are 4 elements here.


Firstly have a macro which invokes Ontime to run a macro that saves the
workbook in 5 minutes. And you also need a macro to save the workbook and
reset the timer


Option Explicit


Public nSaveWB As Date


Public Sub SetSaveWBTimer()
nSaveWB = Now + TimeSerial(0, 5, 0) ' 5 minutes
Application.OnTime nSaveWB, "SaveWB"
End Sub


Public Sub SaveWB()
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub


You then need to set the timer in the first place, when the workbook opens.
And you also need tyo trap any workbook changes so that the timer gsets
cancelled, and set anew (note this doesn't reset the timer for any
formatting changes, only data changes).


Option Explicit


Private Sub Workbook_Open()
SetSaveWBTimer
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.OnTime nSaveWB, "SaveWB", , False
SetSaveWBTimer
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code




--

HTH

RP
(remove nothere from the email address if mailing direct)


"Lee" <[email protected]> wrote in message
news:[email protected]...
> Is it possible to set up an Excel workbook to timeout and close
automatically
> after a given amount of time, say 15 minutes? Maybe via a macro that
times
> out and closes the workbook? Or perhaps a macro from another workbook
that
> can close it down?
>
> The problem we are having in the Company is that one user will open an
Excel
> workbook from our network and forget to close it. Then when someone else
> tries to open it and make modifications they get the "File in Use" dialog
box
> which says "filename.xls is locked for editing." and the only buttons
> available are "Read Only", "Notify", or "Cancel".

Excellent, Bob. This works great! It's exactly what I was looking for.
Thanks a million.
Regards,
Lee

"Bob Phillips" wrote:

> You could use Ontime together with worksheet change event code to monitor
> it.
>
>
> There are 4 elements here.
>
>
> Firstly have a macro which invokes Ontime to run a macro that saves the
> workbook in 5 minutes. And you also need a macro to save the workbook and
> reset the timer
>
>
> Option Explicit
>
>
> Public nSaveWB As Date
>
>
> Public Sub SetSaveWBTimer()
> nSaveWB = Now + TimeSerial(0, 5, 0) ' 5 minutes
> Application.OnTime nSaveWB, "SaveWB"
> End Sub
>
>
> Public Sub SaveWB()
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> End Sub
>
>
> You then need to set the timer in the first place, when the workbook opens.
> And you also need tyo trap any workbook changes so that the timer gsets
> cancelled, and set anew (note this doesn't reset the timer for any
> formatting changes, only data changes).
>
>
> Option Explicit
>
>
> Private Sub Workbook_Open()
> SetSaveWBTimer
> End Sub
>
>
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> Application.OnTime nSaveWB, "SaveWB", , False
> SetSaveWBTimer
> End Sub
>
>
> 'This is workbook event code.
> 'To input this code, right click on the Excel icon on the worksheet
> '(or next to the File menu if you maximise your workbooks),
> 'select View Code from the menu, and paste the code
>
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Lee" <[email protected]> wrote in message
> news:[email protected]...
> > Is it possible to set up an Excel workbook to timeout and close
> automatically
> > after a given amount of time, say 15 minutes? Maybe via a macro that
> times
> > out and closes the workbook? Or perhaps a macro from another workbook
> that
> > can close it down?
> >
> > The problem we are having in the Company is that one user will open an
> Excel
> > workbook from our network and forget to close it. Then when someone else
> > tries to open it and make modifications they get the "File in Use" dialog
> box
> > which says "filename.xls is locked for editing." and the only buttons
> > available are "Read Only", "Notify", or "Cancel".
>
>
>

Excellent, Bob. This works great! It's exactly what I was looking for.
Thanks a million.
Regards,
Lee

"Bob Phillips" wrote:

> You could use Ontime together with worksheet change event code to monitor
> it.
>
>
> There are 4 elements here.
>
>
> Firstly have a macro which invokes Ontime to run a macro that saves the
> workbook in 5 minutes. And you also need a macro to save the workbook and
> reset the timer
>
>
> Option Explicit
>
>
> Public nSaveWB As Date
>
>
> Public Sub SetSaveWBTimer()
> nSaveWB = Now + TimeSerial(0, 5, 0) ' 5 minutes
> Application.OnTime nSaveWB, "SaveWB"
> End Sub
>
>
> Public Sub SaveWB()
> ActiveWorkbook.Save
> ActiveWorkbook.Close
> End Sub
>
>
> You then need to set the timer in the first place, when the workbook opens.
> And you also need tyo trap any workbook changes so that the timer gsets
> cancelled, and set anew (note this doesn't reset the timer for any
> formatting changes, only data changes).
>
>
> Option Explicit
>
>
> Private Sub Workbook_Open()
> SetSaveWBTimer
> End Sub
>
>
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> Application.OnTime nSaveWB, "SaveWB", , False
> SetSaveWBTimer
> End Sub
>
>
> 'This is workbook event code.
> 'To input this code, right click on the Excel icon on the worksheet
> '(or next to the File menu if you maximise your workbooks),
> 'select View Code from the menu, and paste the code
>
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Lee" <[email protected]> wrote in message
> news:[email protected]...
> > Is it possible to set up an Excel workbook to timeout and close
> automatically
> > after a given amount of time, say 15 minutes? Maybe via a macro that
> times
> > out and closes the workbook? Or perhaps a macro from another workbook
> that
> > can close it down?
> >
> > The problem we are having in the Company is that one user will open an
> Excel
> > workbook from our network and forget to close it. Then when someone else
> > tries to open it and make modifications they get the "File in Use" dialog
> box
> > which says "filename.xls is locked for editing." and the only buttons
> > available are "Read Only", "Notify", or "Cancel".
>
>
>