Results 1 to 5 of 5

Thread: How to run a macro at a particular time, then re-run it at specified interval and end it at a particular time?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2007
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to run a macro at a particular time, then re-run it at specified interval and end it at a particular time?

    Dear All,

    I wish to run an excel macro at a particular time, say 7:00 am and would like to run it at a specified interval, say every hour till a particular time, say 5:00 PM. What could be the appropriate code for it? My start time, end time and interval all three would be variables. But I will be still happy if I come across a code involving constants, at least.

    Any help will be truly appreciated! Thanks in advance!

    Regards,
    Nachiket

  2. #2
    Board Regular ParamRay's Avatar
    Join Date
    Aug 2014
    Location
    England, UK
    Posts
    1,193
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to run a macro at a particular time, then re-run it at specified interval and end it at a particular time?

    this is kind the pattern you need:

    Code:
    Dim NextRun As Date
    
    
    Sub StartSchedule()
    ' This procedure sets the first run for 07:00
      NextRun = TimeSerial(7, 0, 0)
      Application.OnTime NextRun, "MyMacro"
    End Sub
    
    
    Sub MyMacro()
    ' ==============================
    ' Your processing code goes here
    ' ==============================
    
    ' The next part schedules another run in an
    ' hour's time, but only if it's before 17:00
    
      If Now() < Date + TimeSerial(17, 0, 0) Then
        NextRun = Now() + TimeSerial(1, 0, 0)
        Application.OnTime NextRun, "MyMacro"
      End If
    End Sub
    
    
    Sub StopSchedule()
    ' This procedure terminates the schedule
      Application.OnTime NextRun, "MyMacro", , False
      
    ' Note that the schedule will
    ' also end once you quit Excel
    End Sub

  3. #3
    Board Regular
    Join Date
    Jan 2007
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to run a macro at a particular time, then re-run it at specified interval and end it at a particular time?

    Brilliant! This was exactly what I wanted! Thank you very much, ParamRay!

  4. #4
    Board Regular
    Join Date
    Jan 2007
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to run a macro at a particular time, then re-run it at specified interval and end it at a particular time?

    Just one query. How should I trigger the startSchedule macro? Should I put it in workbook_open event? Or will it work automatically?

  5. #5
    Board Regular
    Join Date
    Jan 2007
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to run a macro at a particular time, then re-run it at specified interval and end it at a particular time?

    Hi ParamRay,

    I have one question. I developed my code but I am getting the output twice. In other words, the macro runs twice. I am puzzles as to how this happens. Can you please guide me?

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' This procedure terminates the schedule
      On Error Resume Next
      Application.OnTime NextRun, "MyMacro", , False
      On Error GoTo 0
    ' Note that the schedule will
    ' also end once you quit Excel
    
    End Sub
    
    Private Sub Workbook_Open()
    'Sub StartSchedule()
    ' This procedure sets the first run for 07:00
      
    truth_val = Worksheets("Parameters").Cells(8, 39).Value
    If truth_val = True Then Exit Sub
      
      start_hr = Worksheets("Parameters").Cells(5, 2)
      start_hr = Val(start_hr)
      start_min = Worksheets("Parameters").Cells(5, 3)
      start_min = Val(start_min)
      
      NextRun = TimeSerial(start_hr, start_min, 0)
      Application.OnTime NextRun, "MyMacro"
    'End Sub
    
    End Sub
    
    
    Sub MyMacro()
    ' ==============================
    ' Your processing code goes here
    ' ==============================
    
    
    'actual execution
    Dim time_stamp As Date, date_stamp As Date
    
    Application.ScreenUpdating = False
    othfil = ActiveWorkbook.Name
    ThisWorkbook.Activate
    Calculate
    time_stamp = Worksheets("Parameters").Cells(5, 40)
    date_stamp = Worksheets("Parameters").Cells(5, 39)
    Lcol = Worksheets("‹bersicht").Cells(17, Columns.Count).End(xlToLeft).Column
    
    For i = 2 To Lcol
    sheet_no = Worksheets("‹bersicht").Cells(17, i)
    sheet_ref = "H_" & sheet_no
    V1 = Worksheets("‹bersicht").Cells(18, i)
    V2 = Worksheets("‹bersicht").Cells(19, i)
    V3 = Worksheets("‹bersicht").Cells(20, i)
    V4 = Worksheets("‹bersicht").Cells(21, i)
    V5 = Worksheets("‹bersicht").Cells(22, i)
    V6 = Worksheets("‹bersicht").Cells(23, i)
    
    Lrow = Worksheets(sheet_ref).Cells(Rows.Count, "A").End(xlUp).Row + 1
    Worksheets(sheet_ref).Cells(Lrow, 1) = date_stamp
    Worksheets(sheet_ref).Cells(Lrow, 2) = time_stamp
    Worksheets(sheet_ref).Cells(Lrow, 3) = V1
    Worksheets(sheet_ref).Cells(Lrow, 4) = V2
    Worksheets(sheet_ref).Cells(Lrow, 5) = V3
    Worksheets(sheet_ref).Cells(Lrow, 6) = V4
    Worksheets(sheet_ref).Cells(Lrow, 7) = V5
    Worksheets(sheet_ref).Cells(Lrow, 8) = V6
    
    Next i
    
    ' The next part schedules another run in an
    ' hour's time, but only if it's before 17:00
    
    end_hr = Worksheets("Parameters").Cells(9, 2)
      end_hr = Val(end_hr)
      end_min = Worksheets("Parameters").Cells(9, 3)
      end_min = Val(end_min)
      
    int_hr = Worksheets("Parameters").Cells(7, 2)
      int_hr = Val(int_hr)
      int_min = Worksheets("Parameters").Cells(7, 3)
      int_min = Val(int_min)
      
      If Now() < Date + TimeSerial(end_hr, end_min, 0) Then
        NextRun = Now() + TimeSerial(int_hr, int_min, 0)
        Application.OnTime NextRun, "MyMacro"
      End If
      Workbooks(othfil).Activate
      Application.ScreenUpdating = True
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •