Run macro with Windows Task Scheduler

[Noz2k]

Currently I have a task setup which opens my workbook every day at a set time, runs 2 macros in the workbook open procedure and then closes excel.

However as the macros email people, having the code run on the workbook open procedure is not ideal. If the file is opened at any other time these emails will still be sent, which will just confuse people (it also makes it a pain to edit as the procedure closes the workbook.)

Is there a way to use the task scheduler to run specific Macros so I wouldn't have to have them in the workbook open procedure?

 

[Andrew Poulsom]

There's an example here:

http://krgreenlee.blogspot.com/2006/04/excel-running-excel-on-windows-task.html

 

[Noz2k]

Thanks Andrew,

Will have a look and play around with that. On first look I think there could possibly be some issues due to the security settings in the directories, but I'll have a try nonetheless.

 

[klarowe]

If that does work what about something like this:
Create new excel document (something just for this purpose). In that workbook open procedure have it open the other workbook, run the required macro, then close both workbooks. That way when the main workbook is opened there is no workbook open procedure and therefore shouldn't have any issues.

This is simply just a possible work around and hell, I'm not even sure it would actually work, but I figured I'd throw it out there as a possibility...

 

[Noz2k]

Yeah that was a workaround I had thought of, I think it would work, and may look to do that, as I'm struggling with what's in the link posted.

The other workaround I could think of what to put in a countdown when the workbook opens, and exit the sub if for instance "stop" is inputted in cell A1 before the countdown finishes.

 

[KGee]

Yeah that was a workaround I had thought of, I think it would work, and may look to do that, as I'm struggling with what's in the link posted.

I used the basic info in the link posted but I remember it didn't work for me straight out of the box. I had to make some minor changes to get it to work. Without comparing the code side by side I wouldn't remember what I changed but I can send you a sample of what I am currently using that works.

 

[Noz2k]

If you could that'd be great. Would be handy to see a working version rather than a template version, then I can see which bits I need to change.

Thanks,


currently I'm using a countdown which enables me to stop the procedure from running by entering a value into B1 before the countdown finishes.

Public NextTime As Date
Public EndTime As Date
 
Sub auto_open()
 
Sheets("Queries").Range("B1").Clear
EndTime = Now + TimeValue("00:00:15")
NextTime = Now + TimeValue("00:00:01")
ActiveSheet.Range("A1").NumberFormat = "hh:mm:ss"
ActiveSheet.Range("A1").Value = EndTime - Now
Application.OnTime NextTime, "Continuecount"
 
End Sub
 
Sub Continuecount()
 
NextTime = Now + TimeValue("00:00:01")
If EndTime - Now < 0 Then
Call GetDetails
Call SendDetails
ThisWorkbook.Save
ThisWorkbook.Close
Application.Quit
Exit Sub
End If
Sheets("Queries").Range("A1").Value = EndTime - Now
If Sheets("Queries").Range("B1").Value > "" Then
Exit Sub
End If
Application.OnTime NextTime, "Continuecount"
 
End Sub

Works for me, but probably not ideal if anybody else opens the file.

 

[klarowe]

Instead of having to enter a value couldn't you have a messagebox come up and if "Ok" wasn't selected within a certain amount of time then the macro would continue running?

 

[Noz2k]

Most probably, but I didn't know how to put an if condition on whether a message box has been clicked or not.

So just made a big black banner with red lettering across row 1 on the worksheet, instructing that B1 needs a value to stop the procedure. And made the countdown timer pretty clear to see also.

 

[KGee]

If you could that'd be great. Would be handy to see a working version rather than a template version, then I can see which bits I need to change.

Check your PM's.