1

I have been trying to get a powershell script to run as a scheduled task but am not having any luck. I have a spreadsheet that I need to open and save to update some refreshed information to display on a screen. The powershell script runs fine inside of powershell and the file is updated. I tried to create a scheduled task and set the program to powershell.exe and the arguments just list the path and ps1 name. I have tried the -command and -file parameter and also leaving it blank. The task shows it runs successfully but the file is not updated. If I open a command prompt and type in the same info in the task which is powershell.exe c:\scripts\goalupdate.ps1 it will run fine and the file is updated. So I created a batch file with just that command in the batch file but it still does not run in the scheduled task no matter what I try. I am running the task under the same account I am using to manually run it and I have set the password and set to run with highest privileges.

Any idea why the scheduled task will not run the script even though it shows it ran successfully everytime but the file is not updated. It has to be something with the task scheduler as this works fine when running at a command prompt or within powershell.

Thanks.

5 Spice ups
2

https://community.spiceworks.com/how_to/17736-run-powershell-scripts-from-task-scheduler

Does it work when you run it manually? ( I read you said it does)

Add logging to the script

start-transcript -path "c:\log.txt"
    #code here
stop-transcript

if you see a log you at least know the .ps1 kicked off and you can look at the log.

If you see no log, you can tell it did not even run.

Task scheduler does not care what the program does, it just cares that is successfully started the .ps1.

Maybe try it with ‘-executionpolicy bypass’

Does the user you run it as had the right rights to the Excel sheet and the folder where it resides?

EDIT: Typos

3 Spice ups
3

I had to add the argument of “-ExecutionPolicy Bypass PathToScript” to get mine to run.

ps.jpg
ps.jpg800×373 36.4 KB
2 Spice ups
4

Neally and James404d beat me to the answer but you have to point the scheduler to powershell.exe first then the script.

@alexw @jamespierce

5

I have tried the -excecutionpolicy bypass but it still does not work.

6

How about the other suggestions… ?

7

This is what I use:

%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Unrestricted -Command C:\scripts\Random-Script.ps1

That is a .bat file that users it and calls the script.

1 Spice up
8

OK, I enabled the transcript and found errors in the log file that was created inside the powershell script. I will have to decipher those errors.

Odd thing is the script runs fine manually and no errors in the log file if I run it from powershell or the command prompt. Only get errors in the log file when running from the scheduled task.

1 Spice up
9

Well, if you share those errors we might be able to help decipher them.

10

This is one of the errors. I am checking the DCOM configuration now.

PS>TerminatingError(New-Object): “Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE)).”

11

I assume you run this from a different machine than where the Excel is on?

Make sure the account you use to run it has ‘logon as batch job’ rights to that machine.

It might have to do with this:

4) There is a security problem in the way the COM server is started (this page seems to suggest misspelled passwords
 or lacking the "Login as Batch Job" privilege for "Run As.." COM servers, but anyway I would suggest re-verifying
 this information for your specific configuration)
1 Spice up
12

I thought there was an option in excel along the lines of “refresh data automatically when opened”. Would this not fix the problem rather than running a script?

13

Yes, I am using that feature but I don’t always have the file open. I have a report that I run to gets numbers from a sql database and saves that as csv files. The excel file gets that information using the auto refresh and updates my chart in excel that displays on a TV. The system automatically copies the excel file to our sharepoint site and displays it on a power bi dashboard screen. I need a way to update the file locally before it gets uploaded and don’t want to have to open the excel file and leave it open all day or all the time. This script does that for me. It opens up excel in the background which causes the data to refresh, I put a 30 second pause in the script to make sure it gets updated and then save and close excel in the script. It works great if I manually run the script. Just doesn’t work as a scheduled task.

14

OK, so I think you are on to something. This was running under a local account and not a domain account. I tried opening the local group policy and added the local user to the “allow logon as batch…” but that did not work. Still get the COM errors. I changed the account that was running the script to a domain account that has admin rights and I don’t get the COM errors anymore but get a new error message that it can’t get the open property.

Unable to get the Open property of the Workbooks class
At C:\scripts\DeptGoals.ps1:4 char:1

  • $wb = $x1.workbooks.Open($file)

Here is the script in case you need to see what it does. Problem is that it works as long as I don’t schedule it and run it manually.

$file = ‘//servername/Dashboards/DeptGoals.xlsx’
$x1 = New-Object -ComObject “Excel.Application”
$wb = $x1.workbooks.Open($file)
$wb.RefreshAll()
Start-Sleep -Seconds 30
$x1.DisplayAlerts = $false
$wb.Save()
$wb.Close()
$x1.Quit()

15

It seems like either way, you are reliant on a workstation to be running with Excel to complete this task. Since the script is giving you trouble, as a test, you could modify the script to copy the file locally, then run your open/save/close then copy the file back to the share. Or as a test, you could simply write a powershell script to copy the file from the share to local and vice versa to see if it’s a permissions problem.

16

Instead of doing all that ‘com object black magic’ have you tried to look into the ‘importexcel’ module from Doug Finke, it does it all for you…

Maybe read here:

https://stackoverflow.com/questions/7112967/powershell-excel-automation-save-open-fails-in-scheduled-task?rq=1

I personally know nothing about com-objects.

17

I marked your earlier post as best answer as it has something to do with the running a script. I still haven’t solved the problem of running whether the user is logged on or not but I temporarily solved it but unchecking that and only running while the user is logged in. That works. It is just when I try to set it run when I am not logged in is when I have the issues. I will just leave the computer logged in and hopefully this will run correctly all the time.