[SOLVED] Workbook, Save, Close, Re-Open (w/ TimeValue),Repeat

Workbook, Save, Close, Re-Open (w/ TimeValue),Repeat

The macro runs with a button assigned to "CloseMe". It used to run for my needs but doesn't anymore (as I tried using this code in another workbook without success). Now it saves, closes, waits 10sec to reopen, but then closes right away.

Sub CloseMe()
Application.OnTime Now + TimeValue("00:00:10"), "OpenMe"
ThisWorkbook.Close True
End Sub

Sub OpenMe()
Application.OnTime Now + TimeValue("00:10:00"), "OpenMe"
ThisWorkbook.Close True
End Sub

I need the code to save, close, wait 10sec to reopen, stay open for 10min (to collect real time data), and then repeat this process (until I interrupt it manually to stop). Thanks

Solution

The code does what you are asking it to do: a. CloseMe schedules OpenMe for 10 seconds from now and closes the workbook, then b. Excel re-opens the workbook and invokes OpenMe, which schedules itself for 10 minutes from now, then immediately proceeds to close the workbook, and finally Excel resumes at b 10 minutes later, in a loop.

My understanding is that your code has to perform something either in OpenMe or CloseMe, so you do not want to just schedule a call and close the workbook. Additionally, to cycle, one sub needs to schedule the other. In broad terms, you could go along those lines:

Sub CloseMe()
    'Here, do whatever (if anything) must be done just before saving the workbook.
    '...

    'Schedule the OpenMe execution in 10 seconds.
    'I don't understand why you need to close the workbook, but that's not the question.
    Application.OnTime Now + TimeValue("00:00:10"), "OpenMe"
    ThisWorkbook.Close True
End Sub

Sub OpenMe()
    'Here, do whatever (if anything) must be done just as the workbook opens.
    '...

    'Schedule the CloseMe execution in 10 minutes.
    Application.OnTime Now + TimeValue("00:10:00"), "CloseMe"
End Sub