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

Question

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

Answer 1

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

Answer 2

You are calling the OpenMe sub for both Open and Close subs.

If you want this to run automatically, where does close sub get called other than the command button?

Answer 3

@Excelosaurus we are very close. Thanks for explaining this logically on the different subs. Here is the full code. It works but my time stamps are doubling up when it is recording, closing, & re-opening. I am capturing some RTD and in order for the RTD to refresh you need to open and close the workbook. I tried inserting in ActiveWorkbook.ForceFullCalculation = True to avoid the extra open/close subs but the RTD did not recalculate using this so the only way was to run a open/close sub.

Dim NextTime As Double
Sub RecordData()
Dim Interval As Double
Dim cel As Range, Capture As Range
Application.StatusBar = "Recording Started"
Set Capture = Worksheets("Dashboard").Range("C5:K5") 'Capture this row of data
With Worksheets("Journal") 'Record the data on this worksheet
Set cel = .Range("A2") 'First timestamp goes here
Set cel = .Cells(.Rows.Count, cel.Column).End(xlUp).Offset(1, 0)
cel.Value = Now
cel.Offset(0, 1).Resize(1, Capture.Cells.Count).Value = Capture.Value
End With
NextTime = Now + TimeValue("00:01:00")
Application.OnTime NextTime, "RecordData"
End Sub

Sub StopRecordingData()
Application.StatusBar = "Recording Stopped"
On Error Resume Next
Application.OnTime NextTime, "OpenMe", , False
On Error GoTo 0
End Sub

Sub OpenMe()
Call RecordData
Application.OnTime Now + TimeValue("00:10:00"), "CloseMe"
End Sub

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