I currently have a script that allows me to convert any excel spreadsheets within a specific folder to PDF documents which are saved in another folder. The script works great if you run it from Powershell ISE / Console. However, if you run the script by creating a task within Windows Task Scheduler it fails.
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()
I have included Start-Transcript and found the following errors when running the script through task scheduler:
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
My task within Task Scheduler is set to run with admin privileges with the following settings:
Program/Script powershell.exe Add arguments(optional) -ExecutionPolicy Bypass C:\Temp\ZAOCC.ps1
Any help would be appreciated!
