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 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

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
>
>

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

Originally Posted by Tom Ogilvy

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.