CODEWITHSUNDEEP
excelvba
José Antonio Moyano Acosta
José Antonio Moyano Acosta

Reputation: 29

Is it possible to run a macro every day at a time?

I need to know if it is possible to have a macro executed daily at 7:00 am.

Would this be done through a macro, or through some visual basic component?

Upvotes: 0

Views: 5404

Answers (2)

HiPierr0t
HiPierr0t

Reputation: 161

The issue with the Workbook_Open() event is that if you still need to action on your workbook manually during the day, the macro will launch, which can be source of errors depending on what your macro does.

Alternatively, you can use the Windows Task Scheduler as mentionned above to run a VB Script that will run the macro in your workbook.

  Set xlApp = CreateObject("Excel.Application")
  xlApp.DisplayAlerts = False 'turn off alerts
  xlApp.Application.Visible = False  'keeps Excel hidden
  Set xlBook = xlApp.Workbooks.Open("C:\MyPath\MyWorkbook.xlsm", 0, False)
  xlApp.Run "MACRO_NAME"
  xlBook.Save
  xlBook.Close
  xlApp.Quit
  Set xlBook = Nothing
  Set xlApp = Nothing

Just type your script within notepad and save as .vbs (and then have the Windows Task Scheduler to run you .vbs).

Upvotes: 2

Taelsin
Taelsin

Reputation: 1080

There are a couple ways that I know of to do this, neither of which are perfect.

The first is the Application.OnTime method in VBA (documentation here). This method will schedule another method to be run at a specific time. I haven't thoroughly testing this function, so I don't know if the schedule will be preserved across excel restarts or even workbook closures.

Another option is the Windows Task Scheduler(thanks Mistella). You can set up a basic task within the task scheduler to open a specific workbook in excel at a given time at a given interval and have the code you wish to be run within the Workbook's Open event. Within that event you could check the time. If the time is close enough to your designated task scheduler time you could perform the work you needed to do, save the workbook, and close it, all without user interaction.

Upvotes: 1

Related Questions