Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close file automatically
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" wrote in message ... 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". |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Close file automatically
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" wrote in message ... 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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File/Close automatically saving changes without prompt | Excel Discussion (Misc queries) | |||
Copy from another file and close automatically | Excel Discussion (Misc queries) | |||
close without saving automatically | Excel Programming | |||
Automatically close a userform | Excel Programming | |||
VBA code to automatically close file | Excel Programming |