Close & save a file at set time only IF the file is open
 

I'm trying to have a file auto-Close (with a Save) at a set time ever
day IF it's open at that time.
To do this, I've put the following code in 'ThisWorkbook':

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

And I've put this in Module 1:

Sub TimeMacro()
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub

That works fine in that if the file is open, the file is automaticall
saved and closed. However if the file is NOT open at the set tim
(17:15) and Excel is running, Excel tries to re-open the file (gives m
the Enable macro message) to run the macro, and I want to avoid this.
understand why the macro is doing what it's doing, but how can I ge
Excel to not run TimeMacro when the file is closed.
Many thanks,
Cliv

--
Clivey_U
-----------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...fo&userid=3256
View this thread: http://www.excelforum.com/showthread.php?threadid=53778

Close & save a file at set time only IF the file is open
 

You would need to cancel the scheduled event in the BeforeClose event.
However, the user could possibly cancel when prompted to save and the
workbook is never closed.

If this is for you own use, you could put the macro in the personal.xls and
run it from there. Then have the macro check for the workbook being open

http://www.cpearson.com/excel/ontime.htm

for example of cancelling an ontime event.

http://www.cpearson.com/excel/events.htm for an overview of events.

--
Regards,
Tom Ogilvy


"Clivey_UK" wrote:


I'm trying to have a file auto-Close (with a Save) at a set time every
day IF it's open at that time.
To do this, I've put the following code in 'ThisWorkbook':

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

And I've put this in Module 1:

Sub TimeMacro()
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub

That works fine in that if the file is open, the file is automatically
saved and closed. However if the file is NOT open at the set time
(17:15) and Excel is running, Excel tries to re-open the file (gives me
the Enable macro message) to run the macro, and I want to avoid this. I
understand why the macro is doing what it's doing, but how can I get
Excel to not run TimeMacro when the file is closed.
Many thanks,
Clive


--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=537788

Close & save a file at set time only IF the file is open
 

Tom,
Many thanks for that. Looks like just what I need. I should have
thought of using BeforeClose.
Clive

Tom Ogilvy Wrote:
You would need to cancel the scheduled event in the BeforeClose event.
However, the user could possibly cancel when prompted to save and the
workbook is never closed.



--
Clivey_UK
------------------------------------------------------------------------
Clivey_UK's Profile: http://www.excelforum.com/member.php...o&userid=32569
View this thread: http://www.excelforum.com/showthread...hreadid=537788