How to set a Excel macro to run during set time?

[ld1414]

Morning all,

Looking for assistance on setting up a Macro to update at a set time every evening. Currently our team has excel sets that perform data pulls every morning using a macro called "Update". I was wondering if I can enter a code into this Macro to run at 3am daily? Can anyone advise how this is done? Do I enter the code and leave the workbook open each night, then come in the morning to a updated version?

Thanks for your help.

 

[Joe4]

Here is one way I have done it:

1. Create a version of the Excel workbook that has a "Workbook_Open" event procedure, which is VBA code that runs automatically upon opening the Excel file.
2. In the "Workbook_Open" event procedure, have it call/run the macro you want to run, and then close the workbook when finished.
3. Use a Scheduler (like "Windows Scheduler") to open this Excel file on the appointed days/times.
This will have to be on a computer that will always be turned on on those days/times. I typically use a Server.

As long as you make sure that Macros are enabled when opened from that computer (server), the code should run and close itself.

There are also ways to use Command Line to open the Excel file and run a particular macro from Windows Scheduler (then you don't need to use "Workbook_Open" or have a separate copy of the file).

 

[Eric W]

Joe's method is probably safer, but you can also use the OnTime method. Leave your workbook open when you leave, run a 1-line macro with the OnTime instruction before you leave for the day, and it should run the macro when desired.

https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime

 

[ld1414]

Joe's method is probably safer, but you can also use the OnTime method. Leave your workbook open when you leave, run a 1-line macro with the OnTime instruction before you leave for the day, and it should run the macro when desired.

https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime

Hi Eric,

So I believe your method is easier for me to use (though I know probably more inefficient). I seem to be having some trouble kicking this off with the code below. Time passes and nothing starts. Am I labeling the "encumbrance marco" correctly?





Private Sub Workbook_Open()


Application.OnTime TimeValue("10:49:15"), "Encumbrance_Macro"


End Sub




Sub Encumbrance()
'
' Encumbrance Macro

 

[Eric W]

Is your macro in the same module, and the same workbook? Check out section 1.2 on this page:

http://www.snb-vba.eu/VBA_Application.OnTime_en.html

 

[ld1414]

This was perfect and it's now running. Thank you. I suppose last question for everyone is if the computer is locked, but profile and excel sheet still open. I imagine this will still run overnight correct? Thank you all again. This will save our team tons of time.

 

[Eric W]

It should, although that sounds easy enough to test. You may want to actually check the first time it runs. Glad we could help.

 

[Eric W]

Another thought - you should also make sure that the sleep settings on your PC don't make the PC go to sleep before you want it to.