CODEWITHSUNDEEP
excelvba
petepedropetey
petepedropetey

Reputation: 11

Automatically run macro at 4 points in the day

I am trying to run a macro 4 times in a day. 10:10 am, 12:25 pm, 15:25 PM and 18:15 PM.

This excel file is opened in the morning and is open all day. The following day the file is re-saved under a new date name and is open all day.

I think I got it working to automatically update once, but it doesn't seem to work for the rest. I used what I found online to make it automatically run once at 10:10 am.

This part is in "ThisWorkbook" under "Workbook" and "Open"

Private Sub Workbook_Open()
Application.OnTime TimeValue("10:10:00"), "UpdateManifestpending"
End Sub

Then the next part is in the module that the macro is saved in. The macro just refreshes a data connection.

Sub UpdateManifestpending()

Application.OnTime TimeValue("10:10:00"), "UpdateManifestpending"
'
' UpdateManifestpending Macro
'

'
ActiveWorkbook.Connections("ManifestPendingUpdates").Refresh
End Sub

I would like to have this run, not only just that one time at 10:10, but at the other times of the day listed above. I tried this to have it run 4 times a day but it doesn't seem to work.

In "thisworkbook"

Private Sub Workbook_Open()
Application.OnTime TimeValue("10:10:00"), "UpdateManifestpending"
Application.OnTime TimeValue("12:15:00"), "UpdateManifestpending"
Application.OnTime TimeValue("15:25:00"), "UpdateManifestpending"
Application.OnTime TimeValue("15:25:00"), "UpdateManifestpending"
End Sub'

And under the ModuleID for the macro

`Sub UpdateManifestpending()

Application.OnTime TimeValue("10:10:00"), "UpdateManifestpending"
Application.OnTime TimeValue("12:15:00"), "UpdateManifestpending"
Application.OnTime TimeValue("15:25:00"), "UpdateManifestpending"
Application.OnTime TimeValue("15:25:00"), "UpdateManifestpending"

'
' UpdateManifestpending Macro
'

'
ActiveWorkbook.Connections("ManifestPendingUpdates").Refresh
End Sub

`

I might be misunderstanding how this activate.ontime works Anyhelp is appreciated

Upvotes: 1

Views: 740

Answers (2)

urdearboy
urdearboy

Reputation: 14580

  1. Code 1: A WorkBook_Open which calls your scheduler macro
  2. Code 2: Your scheduler macro which calls the code to be ran at given times
  3. Code 3: your code to be ran at given times
  4. Home Stretch: Validate Security Settings to allow this type series of steps to be executed

Code 1

Under the code space for ThisWorkbook 

Private Sub Workbook_Open()
    Call Scheduler
End Sub

Code 2

Under a Module code space you will have 2 sets of code. 

Sub Scheduler()
    Application.OnTime TimeValue("10:10:00"), "UpdateManifestpending"
    Application.OnTime TimeValue("12:15:00"), "UpdateManifestpending"
    Application.OnTime TimeValue("15:25:00"), "UpdateManifestpending"
    Application.OnTime TimeValue("15:25:00"), "UpdateManifestpending"
End Sub

Code 3

Sub UpdateManifestpending()
    'Code here
End Sub

Home Stretch

You will need to allow programmatic access to the VB project

Navigate to Trust Center > Macro Settings and select Trust access to the VBA project object module

enter image description here

Upvotes: 2

user10773628
user10773628

Reputation:

In a public module, put this dynamic scheduling code.

sub runWhen()

    Select Case Time
        Case Is < TimeSerial(10, 10, 0)
            Application.OnTime TimeSerial(10, 10, 0), "UpdateManifestpending"
        Case Is < TimeSerial(12, 25, 0)
            Application.OnTime TimeSerial(12, 25, 0), "UpdateManifestpending"
        Case Is < TimeSerial(15, 25, 0)
            Application.OnTime TimeSerial(15, 25, 0), "UpdateManifestpending"
        Case Is < TimeSerial(18, 15, 0)
            Application.OnTime TimeSerial(18, 15, 0), "UpdateManifestpending"
    End Select

end sub

In ThisWorkbook,

Private Sub Workbook_Open()
    runWhen
End Sub

Add the scheduling to UpdateManifestpending just before End Sub.

sub UpdateManifestpending()

   'lots of code here

    runWhen
End Sub

Upvotes: 1

Related Questions