Excel macros run through powershell but not when run by windows task scheduler

Question

I have a script which checks a folder for excel files and then if this "threshold" is greater than 0 then a macro from another excel file is run that interact with these excel folders.

When I run the process manually through powershell ISE it works fine but when I use the windows task scheduler the powershell script runs but the excel macro called doesn't run. Any suggestions why this might be the case? This process used to run on a windows 2008 server fine but was migrated to windows server 2012 and won't run properly

  if ($count -gt $threshold){
    $excel = new-object -comobject excel.application

    $workbook = $excel.workbooks.open("D:\TimesheetService\IS-FS - AutoTimesheetLoader v2.3 - UAT.xlsm")

    $worksheet = $workbook.worksheets.item(1)

    $excel.Run("ImportTime")

    $workbook.close($false)

    $excel.quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
    Remove-Variable excel
}

Have a look at superuser.com/questions/579900/…. BTW: Why do you use the MacOS-Tag? — FunThomas

mklement0 mklement0 · Accepted Answer · 2020-02-03T14:52:18

You cannot use the COM Automation Excel library (new-object -comobject excel.application) from a scheduled task, unless that task is run in the window station (session) of the currently logged-on user.

That is, in order to use the COM Excel library from a scheduled task, you must have the Run only when user is logged on option chosen for it, which you may not want, for two reasons:

It restricts your task to run only when someone happens to be logged on.
The currently logged-on user will see the task's window as it runs - unless you take extra steps to hide it (which can't be done with powershell.exe itself).

^{Note: There is a workaround that is currently effective, but it is unsupported, which is why it is better to avoid it - see this answer to a related superuser.com question.}

Therefore, consider alternatives that do not have this restriction, such as the DocumentFormat.OpenXml Nuget package.

See this Microsoft support article for background information.

Excel macros run through powershell but not when run by windows task scheduler

2 Answers