Automating Excel VBA script via. Task Scheduler

dcg38524

Board Regular
Joined
Dec 4, 2013
Messages
113
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)
VBA Code:
Sub SoTime()

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

End Sub

2. script2.vbs (VBScript )
VBA Code:
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)
VBA Code:
cscript script2.vbs "C:\Users\dcg38\Documents\VBA code\TestingAutomatedScript.xlsm"
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
  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

Board Regular
Joined
Dec 4, 2013
Messages
113
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

Board Regular
Joined
Dec 4, 2013
Messages
113
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Glad you sorted it out. Thanks for your feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,015
Messages
5,628,147
Members
416,295
Latest member
jjkh58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top