时间戳

首页 > 解决方案 > Powershell 脚本适用于 ISE/控制台，但不适用于任务计划程序

excel - Powershell 脚本适用于 ISE/控制台，但不适用于任务计划程序

问题描述

我目前有一个脚本，允许我将特定文件夹中的任何 Excel 电子表格转换为保存在另一个文件夹中的 PDF 文档。如果您从 Powershell ISE / 控制台运行该脚本，它的效果很好。但是，如果您通过在 Windows 任务计划程序中创建任务来运行脚本，则会失败。

ZAOCC.ps1

Start-Transcript -Path 'C:\Temp\Log.txt'
Install-Module -Name ImportExcel -Force

$path = 'C:\Temp\Excel'
$path2 = 'C:\Temp\PDF'
$xlFixedFormat = 'Microsoft.Office.Interop.Excel.xlFixedFormatType' -as [type]
$excelFiles = Get-ChildItem -Path $path -include *.xls, *.xlsx -recurse
$objExcel = New-Object -ComObject excel.application
$objExcel.visible = $false
$date = Get-Date -Format 'dd.MM.yyyy'


foreach($wb in $excelFiles)
{
    $filepath = Join-Path -Path $path2 -ChildPath ('Mine Control Record - ' + $date + '.pdf')
    $workbook = $objExcel.workbooks.open($wb.fullname, 3)
    $workbook.Saved = $true
    "Saving $filepath"
    $workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepath)
    $objExcel.Workbooks.close()
}

$objExcel.Quit()

我已包含 Start-Transcript 并在通过任务计划程序运行脚本时发现以下错误：

Microsoft Excel cannot access the file 'C:\Temp\Excel\Test.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.
At C:\Temp\ZAOCC.ps1:16 char:5
+     $workbook = $objExcel.workbooks.open($wb.fullname, 3)
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
Microsoft Excel cannot access the file 'C:\Temp\Excel\Test.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.
At C:\Temp\ZAOCC.ps1:16 char:5
+     $workbook = $objExcel.workbooks.open($wb.fullname, 3)
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

The property 'Saved' cannot be found on this object. Verify that the property exists and can be set.
At C:\Temp\ZAOCC.ps1:17 char:5
+     $workbook.Saved = $true
+     ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound
The property 'Saved' cannot be found on this object. Verify that the property exists and can be
set.
At C:\Temp\ZAOCC.ps1:17 char:5
+     $workbook.Saved = $true
+     ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

Saving C:\Temp\PDF\Mine Control Record - 10.07.2020.pdf
You cannot call a method on a null-valued expression.
At C:\Temp\ZAOCC.ps1:19 char:5
+     $workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepat ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
You cannot call a method on a null-valued expression.
At C:\Temp\ZAOCC.ps1:19 char:5
+     $workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $filepat ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

PS>$global:?
True

我在任务计划程序中的任务设置为使用以下设置以管理员权限运行：

程序/脚本powershell.exe 添加参数（可选） -ExecutionPolicy 绕过 C:\Temp\ZAOCC.ps1

任何帮助，将不胜感激！

标签： excelpowershelltaskscheduler

解决方案

解决方案原来是一个excel错误。在此处遵循解决方案：Powershell script cannot access a file when run as a Scheduled Task

推荐阅读