Running an excel macro from task scheduler

Question

I am trying to run a macro using task scheduler and it looks like it executes the VBScript file but it doesn't actually execute any of the code in the macro.

The pathway for the workbook is C:\Users\cdurrell\Desktop\Test Auto 1.xlsm

The macro is called TestAuto

And the Script file has the pathway C:\Users\cdurrell\Desktop\TestAutoScript.txt

I originally was using the following script file

'Write Excel.xls  Sheet's full path here
strPath = "C:\Users\cdurrell\Desktop\Test Auto 1.xlsm" 

'Write the macro name - could try including module name
strMacro = "Update" '    "Sheet1.TestAuto" 

'Create an Excel instance and set visibility of the instance
Set objApp = CreateObject("Excel.Application") 
objApp.Visible = True   '   or False 

'Open workbook; Run Macro; Save Workbook with changes; Close; Quit Excel
Set wbToRun = objApp.Workbooks.Open(strPath) 
objApp.Run strMacro     '   wbToRun.Name & "!" & strMacro 
wbToRun.Save 
wbToRun.Close 
objApp.Quit 

'Leaves an onscreen message!
MsgBox strPath & " " & strMacro & " macro and .vbs successfully completed!",         vbInformation

'

and it would make the excel workbook read only and not execute the code in the macro.

Then I took out the line

wbToRun.Save

and now it doesn't make the excel workbook read only but it still doesn't execute the code.

Any pointers or corrections are welcome! Thanks!

Are you running the Task Scheduler task under your user login? Are you logged in when this runs? If you just run the vbscript directly does it succeed in running the Excel macro? — Tim Williams
Yes and yes. And I have it set to only run when logged in too. As far as running it directly, I can't get it to find the file so it won't attempt it. I don't know how to run it from command prompt so I'm sure it's my fault. — Cassandra Durell
You can just double-click the vbs file. Get that working before involving the scheduler — Tim Williams
I get the error that it can't find the file. Is it possible it was moved, renamed, or deleted. — Cassandra Durell

Federico Navarrete Federico Navarrete · Accepted Answer · 2020-11-24T17:24:29

I'm not sure if you are still facing this issue, but I found my own solution since I was experiencing the same trouble. It is based on this old answer in Super User by squillman:

https://superuser.com/a/579901

Open Component Services (Start -> Run, type in dcomcnfg)
Drill down to Component Services -> Computers -> My Computer and click on DCOM Config
Right-click on Microsoft Excel Application and choose Properties
In the Identity tab select This User and enter the ID and password of an interactive user account (domain or local) and click OK

Now, what else I needed to do?

Configure the scheduled task with the same account that I configured in step 4. I tried with the service account and failed.
Run whether user is logged or not.

After this, I was able to run it properly.

Running an excel macro from task scheduler

2 Answers