How can you run an Excel macro through a schedule task

Question

I need to run a macro in an Excel spreadsheet through a schedule task. I've defined the job as below:

"C:\Program Files\Microsoft Office\Office10\EXCEL.EXE" ""

The problem is that when the job starts Excel displays a pop-up and asks if we want to disable/enable the macros, which blocks the execution of the job.

How can I run this macro without having to manually click on enable macro?

Answer 1

Write a short vbscript which launches Excel, loads your workbook and uses Application.Run to run the macro. Have your scheduled task run the vbscript.

Eg: http://www.mrexcel.com/forum/showthread.php?t=302970

Answer 2

You do not specify what version of Excel or what version of Windows (or Macintosh) so I will specify that I am using Excel 2010 from Office 32-bit on Windows 7 Ultimate 64-bit...

---

If you want to keep your workbook open and run code at a specified time, later on, then try this..

Insert a new code module to your project -or- use one that you already have in your project.

Create a scheduler subroutine as shown below:

Sub Scheduler()

'-- RUNS SUB(S) (OR FUNCTIONS) AT TIME SCHEDULED.

    Application.OnTime TimeValue("11:46:40"), "TheScheduledSub"

End Sub

Create a subroutine (or function) that you want to run. Here is one for this example:

Sub TheScheduledSub()

    Debug.Print "TheScheduledSub() has run at " & Time

End Sub

Scheduler() will execute TheScheduledSub() at the exact time specified inside Scheduler(), defined by TimeValue. You can read more about TimeValue here.

You could probably pass the time value you want the Scheduler() sub to execute your sub(s) or function(s) in as a parameter in the sub.

You could probably run a sub 5 hours from now, lets say. I.E. Application.OnTime Now + TimeValue("05:00:00"), "TheScheduledSub"

or maybe a day from now? Play around with it.

I did get two subs to execute at different times. I also tried to execute two at the same time. My experience with two different subs executing at the same time was that the second sub seemed to execute before the first, but I havent fully explored it. So YMMV.

I do not think that Excel VBA is multithread capable so keep that in mind. I am running the 32-bit Excel 2010 from the Office suite. Not sure how the 64-bit Office Excel behaves or for that matter any other version or platform. Anyway, multithreading / multitasking Excel VBA is not what you asked but it is to be considered.

Seeing this question and playing with the above I can think of a few ideas I want to try myself!