Excel宏通过Powershell运行，但不是由Windows Task Scheduler运行时运行 [英] Excel macros run through powershell but not when run by windows task scheduler

问题描述

我有一个脚本，该脚本检查文件夹中的excel文件，然后如果此阈值"大于0，则运行另一个与这些excel文件夹交互的excel文件的宏.

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.

当我通过powershell ISE手动运行该进程时，它运行良好，但是当我使用Windows任务计划程序时，powershell脚本会运行，但是调用的excel宏不会运行.有什么建议为什么可能是这种情况?此过程曾经可以在Windows 2008服务器上正常运行，但已迁移到Windows Server 2012，无法正常运行

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
}

推荐答案

您无法使用来自以下位置的COM Automation Excel库( new-object -comobject excel.application )计划的任务，除非该任务在当前登录用户的窗口站(会话)中运行 .

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.

也就是说，要使用计划任务中的COM Excel库，必须为两个用户选择仅在用户登录后才运行选项，而您可能不想这样做.原因:

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.

当前登录的用户将在任务运行时查看-除非您采取其他措施将其隐藏(使用 powershell.exe无法完成)本身).

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).

^{注意:目前有一种有效的解决方法，有效，但是不受支持 ，这就是为什么>更好地避免我 t-请参阅有关超级用户相关问题的此答案.}

^{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.}

因此，考虑没有此限制的替代方案，例如请参见此Microsoft支持文章以获取背景信息.

See this Microsoft support article for background information.

这篇关于Excel宏通过Powershell运行，但不是由Windows Task Scheduler运行时运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！