Close Workbook with Application.OnTime

Hi shg,

Thanks as always for the help. I have read through the ontime method section of help, and it seems that I was missing the call to procedure argument? As everything else is optional?

I have modified my code to this, and it compiles, but still does not run at the scheduled time.

Please Login or Register  to view this content.

I had originally started with and tried to modify the below code, which I found on another site, but had tried to leave off the "OpenMe" argument since I just wanted to close the file.

Please Login or Register  to view this content.

Thanks to both of you, and sorry for the delayed reply, I just now got back in front of the Excel workbook and was able to try a few more things.

shg, I appreciate that you are trying to lead me to the solution, without telling it to me outright, as I do want to learn to be a little more self sufficient with VBA...but I'm afraid I'm still not getting it. Based on your hint I tried to play around with the formatting of the time value, first removing the space before the PM. I tried 12:05PM as well as 12:05:00PM. My latest version has dropped the PM entirely, and instead is using a 24hr clock as per the examples in help.

Please Login or Register  to view this content.

I'm still not having any luck. I assume I still have my format wrong and have misinterpreted your hint...but all the examples I'm finding on-line seem to use the same format, so I'm definitely confused.

If on the off chance that my format is right, could it be another process that is interfering with letting ontime run? I've read that it will not run if another macro is running, but what if the sheet is just constantly recalculating? (This is a real time feed sheet that is constantly updating info from Bloomberg)

Originally Posted by shg

In Excel date/time, the whole number is days since 0 Jan 1900, and the decimal portion is time. You're scheduling the macro to run on New Year's Eve 1900. Try scheduling for Date + Time instead of just Time.

From the VBA help under OnTime Method I see

This example runs my_Procedure at 5 P.M.

Please Login or Register  to view this content.

My understanding was this should run "my procedure" everyday at 5pm? Would the example just run "my procedure" once, on 1/1/1900 at 5pm? And since that time has obviously passed, it wouldn't run at all?

In addition, if Excel remains open, but the workbook was closed earlier (but after the close task was scheduled), the workbook will reopen, just so it can close -- like the nurse waking you up in the hospital to give you a sleeping pill.

I don't mind if this happens, and in fact it is probably preferable that it works that way, as I would like to make sure that it is open before I close it.

Originally Posted by romperstomper

Your code should work fine - if you don't specify the day, today will be assumed.
How are you running the CloseMe sub? Manually, via a menu, shortcut key or what?

Via an Open Workbook event. I had finally gotten it to run partially in a test document, but was having a problem reopening the file again after the macro ran (it would just auto close as soon as it opened). I had added in a

Please Login or Register  to view this content.

argument, and getting rid of the Date + part and going back to how I had it originally seems to have worked. I'm definitely going to have to run with this a few days to gain some confidence, but for anyone else who is looking to do this. Here is what I ended up with:

In ThisWorkbook

Please Login or Register  to view this content.

In a Module

Please Login or Register  to view this content.

Thanks for the suggestion. I'd originally tried a simple reboot macro that I'd found.

Please Login or Register  to view this content.

The workbook that this is for has a number of cells that update realtime as the sheet is open, but there is one table that I've only been able to update by closing and reopening the sheet. I thought I could use the above code to quickly restart the workbook, but when the sheet reopened the links don't update (which is weird because normally they update even if I tell them to not update).

Anyway, I think I'll get better results using a combination of a scheduled task and a batch file to launch the workbook a few minutes after I close it.

Argh...One more question.

The whole point of doing this is so that I can run a macro when I open the file with the batch file...but can I have multiple workbook open events in one workbook? One that will run whenever the workbook is open, but the second one that I can set to just run if the workbook is open at a given time? Or do I just use one Workbook Open and put both calls in one event? Something like this?

Please Login or Register  to view this content.

Originally Posted by romperstomper

You can only have one Workbook_Open event.

So would something like I have in the above post not work? I've been trying to test, but it involves a lot of moving parts, and getting everything changed at one time has been proving difficult (I accidentally broke my workbook and lost all macros and had to restore from a backup)

If I can't put two arguments in a workbook_open event, any other suggestions on how to get one macro to run at one time during the day, and another one to run at another time without any human interaction?

Thank you all very much. I just had my first successful test. Just need to change the times now (hope that doesn't screw anything up) and then set about figuring out how to add in even more useful features...but think I'll wait a couple of days for that.

Marking this thread as closed.