Powershell脚本在作为计划任务运行时不能访问文件

My Powershell (2.0) script has the following code snippet:

我的Powershell(2.0)脚本有以下代码片段:

$fileName = "c:\reports\1.xlsx"
$xl = new-object -comobject excel.application
$xlFormat = [Microsoft.Office.Interop.excel.XlFileFormat]::xlWorkbookDefault
$xl.displayalerts = $false
$workbook = $xl.workbooks.open($fileName)
#Code to manipulate a worksheet
$workbook.SaveAs($fileName, $xlformat)
$xl.quit()
$error | out-file c:\reports\error.txt

I can run this script in the Powershell command prompt with no issues. The spreadsheet gets updated, and error.txt is empty. However, when I run it as a task in Task Scheduler, I get errors with the first line.

我可以在Powershell命令提示符中运行这个脚本，不会出现任何问题。电子表格将被更新，并出现错误。三是空的。但是，当我在task Scheduler中运行它时，第一行会出现错误。

Exception calling "Open" with "1" argument(s): "Microsoft Office Excel cannot access the file 'C:\reports\1.xlsx'. There are several possible reasons: The file name or path does not exist. The file is being used by another program. The workbook you are trying to save has the same name as a currently open workbook.

使用“1”参数调用“Open”的异常:“Microsoft Office Excel无法访问文件‘C:\report \1.xlsx’。”有几个可能的原因:文件名或路径不存在。另一个程序正在使用这个文件。您正在尝试保存的工作簿与当前打开的工作簿同名。

I run the task with the same credentials I use to run the script in the Powershell command prompt. When I run the script manually, it can open, update, and save the spreadsheet with no issues. When I run it in Task Scheduler, it can't access the spreadsheet.

我使用在Powershell命令提示符中运行脚本时使用的凭证运行任务。当我手动运行脚本时，它可以打开、更新和保存电子表格，没有问题。当我在Task Scheduler中运行它时，它无法访问电子表格。

The file in question is readable/writeable for all users. I've verified I can open the file in Excel with the same credentials. If I make a new spreadsheet and put its name in as the $filename, I get the same results. I've verified that there are no instances of Excel.exe in Task Manager.

该文件对所有用户都可读/可写。我已经验证过我可以用相同的凭证在Excel中打开文件。如果我创建一个新的电子表格并将它的名称作为$filename，我将得到相同的结果。我已经证实没有Excel的实例。exe在任务管理器。

Oddly, if I use get-content, I don't have any problems. Also, if I make a new spreadsheet, I don't have any problem.

奇怪的是，如果我使用get-content，我没有任何问题。另外，如果我做一个新的电子表格，我没有任何问题。

$fileName = "c:\reports\1.xlsx"
$xl = get-content $spreadsheet
$xl = new-object -comobject excel.application
$xlFormat = [Microsoft.Office.Interop.excel.XlFileFormat]::xlWorkbookDefault
$xl.displayalerts = $false
# Commented out $workbook = $xl.workbooks.open($fileName)
$workbook = $xl.workbooks.add()
#Code to manipulate a worksheet
$workbook.SaveAs($fileName, $xlformat)
$xl.quit()
$error | out-file c:\reports\error.txt

That works fine. So Get-ChildItem can open the file with no issue. ComObject can open the file if I run it manually, but not if it's run as task.

工作的很好。所以Get-ChildItem可以毫无问题地打开文件。如果我手动运行的话，ComObject可以打开这个文件，但是如果它是作为任务运行的话就不行。

I'm at a loss. Any ideas?

我亏本。什么好主意吗?

2 个解决方案

#1

I think you've hit a bug in Excel:

我认为你在Excel中遇到了一个问题:

You have to create a folder (or two on a 64bit-windows):

您必须创建一个文件夹(或在64位窗口上创建两个):

(32Bit, always)

(32位,总是)

C:\Windows\System32\config\systemprofile\Desktop

C:\Windows\System32\config\ systemprofile \桌面

(64Bit)

(64位)

C:\Windows\SysWOW64\config\systemprofile\Desktop

C:\Windows\SysWOW64\config\ systemprofile \桌面

I have had the same problem and this was the only solution i have found.

我也遇到过同样的问题，这是我唯一找到的解决办法。

From TechNet Forums (via PowerShell and Excel Issue when Automating )

来自TechNet论坛(自动运行时通过PowerShell和Excel问题)

#2

The solutions above didn't work in my SCSM 2012 Scorch environment, instead I used PSExcel (https://github.com/RamblingCookieMonster/PSExcel) which has no dependency on having Excel installed or the ComObject.

上面的解决方案在我的SCSM 2012焦化环境中不起作用，相反，我使用了PSExcel (https://github.com/RamblingCookieMonster/PSExcel)，它不依赖于安装Excel或ComObject。

#1