Can A Macro Start A Closed Excel Workbook?

[rockyw]

Scheduler will not work on our network. Can a macro open a closed workbook? Thanks

 

[Joe4]

I haven't used "OnTime" much (I usually use Windows Scheduler).
In your code, it looks like you have it set to 1:00 AM, so it wouldn't run until then.
Are you changing the time in the code so that the time it is supposed to run passes?

 

[bobsan42]

ontime needs a specific time to run so if you need to run it after 1 minute you have to do it like this:

Sub RunOnTime()
    Application.OnTime Now+TimeValue("00:01:00"), "openBK"
End Sub

if you don't add NOW() it will run at 1 minute past midnight. Or you can also use TIME instead of NOW.

And the workbook, containing the code, must stay open the entire time.

 

[JoeMo]

ontime needs a specific time to run so if you need to run it after 1 minute you have to do it like this:

Sub RunOnTime()
    Application.OnTime Now+TimeValue("00:01:00"), "openBK"
End Sub

if you don't add NOW() it will run at 1 minute past midnight. Or you can also use TIME instead of NOW.

And the workbook, containing the code, must stay open the entire time.

Once the OnTime code is run, the workbook containing the OnTime code can be closed, but the Excel Application must remain open. If the Excel Application remains open, the code-containing workbook will be opened at the scheduled time and the procedure it triggers will run.

 

[rockyw]

Maybe I'm not doing this right. I have all the code in a workbook that will stay open and all in a module. This workbook will stay open 24/7. I would like to open the workbook at midnight and at 6 am. I don't understand how the time is set. Does Some of this code need to be in the workbook that will be opened? Thanks

 

[JoeMo]

The module Joe4 posted in post #9 should open the workbook at one minute past midnight provided you run the sub RunOnTime prior to that time. You need another ontime procedure to open the workbook at 6 AM. If the workbook is already open you may get a run time error or at least an alert will be displayed. You can avoid either by using a simple function to test for the workbook being open. Something like this:

Sub RunOnTime()
    Application.OnTime TimeValue("00:01:00"), "openBK"
End Sub
Sub openBk()
    If Not WorkbookOpen("End Line Maintenance Dashboard Master.xlsm") Then
        Workbooks.Open "O:\QACOM\Production Reports\End Line Maintenance Dashboard Master.xlsm"
    End If
End Sub
Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Application.Workbooks(WorkBookName).Name) > 0 Then
        WorkbookOpen = True
        Exit Function
    End If
WorkBookNotOpen:
End Function

 

[bobsan42]

I stand corrected

 

[rockyw]

Thanks for all the help and suggestions. I id fin this that will open the work book every what ever its set at. Thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", , False
 End Sub
Private Sub Workbook_Open()
Application.OnTime Now + TimeV
alue("00:00:30"), "MyMacro"
 End Sub

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:30")
Application.OnTime dTime, "MyMacro"
Workbooks.Open "C:\Applications\BookTo Open.xlsm"
End Sub