Alternative to Scheduled Tasks?

[VegasNath]

Hello,

Admin, please move if you feel appropriate....

For security reasons (???), our company has disabled scheduled tasks within windows XP, so I am looking for an alternative. What I would like to do is run a BATch file at 07:45am daily, scheduled tasks seemed the obvious route, but this is not currently an option.

Is there a BATch or VBS or a.n.other alternative to schedule a BATch execution daily?

[HansV]

You could use an Excel workbook that remains open all the time.

In a standard module:

Code: Select all

Public dtmRun As Date

Sub MyMacro()
  Shell "C:\Test\Something.bat"
  dtmRun = TimeSerial(7, 45, 0)
  Application.OnTime dtmRun, "MyMacro"
End Sub

and in the ThisWorkbook module (to start the whole thing when the workbook is opened):

Code: Select all

Private Sub Workbook_Open()
  dtmRun = TimeSerial(7, 45, 0)
  Application.OnTime dtmRun, "MyMacro"
End Sub

[VegasNath]

Hans, I tried this out, changing the time to 20:10 in both macro's and I get the following error:

".Ontime" is highlighted.

Capture.JPG

[StuartR]

You have not included the name of the macro in the Application.OnTime line
Change
Application.Ontime dtmRun
to
Application.Ontime dtmRun MyMacro

[HansV]

Stuart is correct - I changed the name of the macro, started to modify the code then got distracted. I have edited my previous reply.

[VegasNath]

OK, Thanks. Whilst I was waiting on a reply, I commented out lines 2&3 of "MyMacro" and the process appears to work without them. Why is this? I cannot get my head around the need for the 2 lines if the workbook open event fires the shell at the specified time?

[StuartR]

These two lines reschedule the task to run again the next day

[VegasNath]

Aha, makes perfect sense! Thanks Stuart & Hans.

[jonwallace]

You didn't say if IT had disabled installing software. If not try this. I use it for a number of repeating tasks at work.

[John Gray]

Another way could be to start a calling BATch file, and within it check the time every minute to see if it's 07:45 yet.
If it is, CALL the 'real' BATch file and return to the caller, which should wait for (say) 23 hours-worth of seconds (to some time tomorrow well before 07:45).
If it isn't, wait another 50 seconds (say), and loop round to try again.

Waits can be implemented using PING in the form PING -n 999 127.0.0.1 > nul
My figure 999 means that it will PING every second for 998 seconds (n-1), so waiting 50 seconds would be coded as PING -n 51 127.0.0.1 > nul

Other WAIT mechanisms are available, but this one is effectively 'built-in'.

LATER... I remind myself that some geeky computer magazine produced a T-shirt bearing the text:
There's no place like 127.0.0.1
For IPv6, this will have to be updated to:
There's no place like ::1
(or 0:0:0:0:0:0:0:1)

[VegasNath]

You didn't say if IT had disabled installing software. If not try this. I use it for a number of repeating tasks at work.

Thankyou very much Jon, this is well worth consideration for another task I have in mind.

[VegasNath]

Another way could be to start a calling BATch file, and within it check the time every minute to see if it's 07:45 yet.
If it is, CALL the 'real' BATch file and return to the caller, which should wait for (say) 23 hours-worth of seconds (to some time tomorrow well before 07:45).
If it isn't, wait another 50 seconds (say), and loop round to try again.

Waits can be implemented using PING in the form PING -n 999 127.0.0.1 > nul
My figure 999 means that it will PING every second for 998 seconds (n-1), so waiting 50 seconds would be coded as PING -n 51 127.0.0.1 > nul

Other WAIT mechanisms are available, but this one is effectively 'built-in'.

LATER... I remind myself that some geeky computer magazine produced a T-shirt bearing the text:
There's no place like 127.0.0.1
For IPv6, this will have to be updated to:
There's no place like ::1
(or 0:0:0:0:0:0:0:1)

Hi John,
Thankyou for your response. For now, I am hoping that Hans solution above will suit my current needs, however a BATch alternative is still of interest for future reference. Would you mind providing a small demonstration of how 'waits' could be used to call another BAT on a specified date & time? TIA!

[VegasNath]

Code: Select all

Private Sub Workbook_Open()
 
    dtmRun = TimeSerial(7, 45, 0)
    Application.OnTime dtmRun, "Bat_Schedule"
 
End Sub

I have noticed that on occasions, the code executes twice at the same time. Is there a way to stop this from happening please?

[HansV]

Could this occur because the workbook has been opened twice? If so, you could try this:

Code: Select all

Private Sub Workbook_Open()
    dtmRun = TimeSerial(7, 45, 0)
    On Error Resume Next
    ' Kill scheduled run if present
    Application.OnTime dtmRun, "Bat_Schedule", , False
    ' Schedule run
   On Error GoTo 0
    Application.OnTime dtmRun, "Bat_Schedule"
End Sub

[VegasNath]

Could this occur because the workbook has been opened twice? ....

I have no idea what causes the occasional duplicate. Thanks for the solution!