Automating Excel VBA script via. Task Scheduler

[dcg38524]

Hello,

I been attempting to auto run a Excel VBA script every week using the Windows 10 Task Scheduler with no success. Now I am at the limitation of my Task Scheduler skills needing your help to solve this issue.

I've created 3x files Excel xlsm, VBS & batch file (see all 3x code sections listed at the end of this posting)

• Running the bat file outside of task scheduler all seem to work as expected.
• But once I include the batch file"run.bat" in Task Scheduler the file seems to run but the Excel section doesn't execute at all and no Errors been generated to understand why.

Any help or advice would be greatly appreciated.

Best regards,
Don

1. Excel VBA script (configured to run once the excel file is opened)

Sub SoTime()

Application.DisplayAlerts = False
MsgBox "Today is " & Format(Date, "dddd - mm/dd/yyyy" & " " & Format(Time, "hh:mm"))

End Sub

2. script2.vbs (VBScript )

Dim ObjExcel, ObjWB
Set ObjExcel = CreateObject("Excel.Application")

'ObjExcel.Visible = True

'vbs opens a file specified by the path below
Set ObjWB = ObjExcel.Workbooks.Open("C:\Users\dcg38\Documents\VBA code\TestingAutomatedScript.xlsm")
'either use the Workbook Open event (if macros are enabled), or Application.Run

ObjWB.Close False
ObjExcel.Quit
Set ObjExcel = Nothing

3. run.bat (Batch File)

cscript script2.vbs "C:\Users\dcg38\Documents\VBA code\TestingAutomatedScript.xlsm"

 

[Trevor_S]

It's been some years since I've done this (was on windows NT!) - so don't know if windows 10 will work the same way. But I didn't bother with a bat file. Just get task scheduler to open the excel file (you need the path of the excel exe file, as well as the xlsm file), and put the code you want to run in the Workbook Open event of the xlsm file.

Something like the following?
"C:\Program Files (x86)\Microsoft Office\Office16\EXCEL.EXE" "C:\Documents\myfile.xlsm"

 

[GWteB]

1. The Excel VBA code seems allright to me;
2. I've reason to believe that this VB script will not work properly, although it will not raise any error. Cscript parses your script line by line. It goes on downwards, independent of the previous lines. Excel will launch but even before you are aware of that, Excel will be shut down (except in case you have a slower PC). Inserting a line like cscript.sleep 1000 (i.e. wait 1000 miliseconds) can remedy this phenomenon;
3. The second parameter to cscript in this batch file ("C:\Users\dcg38\...") is not of use, except when you incorporate appropiate lines of code in your VB script to make use of this parameter.

I agree with Trevor_S; don't bather with .cmd, .bat or script files when it's not strictly neccesary.

 

[dcg38524]

Thank you both for your suggestions,

Before posting my original query I've attempted to launch the Excel xlsm script via. Task Scheduler independently without using the bat or VB script but no luck.

Let me add the "cscript.sleep 1000" entry and report back.

Thanks again

 

[dcg38524]

UPDATE:

Unfortunately, adding The "cscript.sleep 1000 " entry did not work as expected but I was able to find a excellent solution on-line(see link below). Following the instructions provided within the article was 100% successfully and now I am able to implement several weekly automated VBS jobs.

How To Automatically Run Excel VBA Macros Daily

Thank you for all your suggestions and help.

Best regards,
Don

 

[GWteB]

Glad you sorted it out. Thanks for your feedback.