Error with VBA "Application.OnTime Now + TimeValue("00:00:05"), Workbooks.Open(pth)"

Question

I am trying to programmatically close and reopen the same excel workbook. I have started with code I found in an old article I found on this site (link for reference).

Application.OnTime Now + TimeValue("00:00:05"), Workbooks.Open(pth)
ThisWorkbook.Close (True)

As coded above, Workbooks.Open(pth) executes immediately. That is not the desired behavior and actually fails in different ways depending on whether (pth) is already opened or whether it represents some other random closed workbook).

I have tried the following (adding quotes to the second parameter):

Application.OnTime Now + TimeValue("00:00:05"), "Workbooks.Open(pth)"
ThisWorkbook.Close (True)

Although this does allow the Ontime method to fully execute, it treats the second parameter (now in quotes) as a macro in the existing workbook, so that after the 5 seconds specified, the following is reported:

Image capture of error

So I am looking for instruction on how I can replace Workbooks.Open(pth) with an actual string argument that the Ontime method is expecting and will result in the workbook specified in (pth) being opened after the specified time elapses.

Answer 1

This would do what you want:

Sub Tester()
    'Schedule `CallMe` to run in 5 seconds, with an argument
    Application.OnTime Now + TimeSerial(0, 0, 5), "'CallMe ""Message Here""'"
    ThisWorkbook.Close True
End Sub

'This will be called from OnTime: if the workbook is closed 
'  it will be re-opened
Sub CallMe(msg)
    MsgBox msg
End Sub