☰

任务计划程序未在 PowerShell 脚本中执行 Start-Sleep

tag-icon • tag-icon powershell sleep scheduled-tasks
任务计划程序未在 PowerShell 脚本中执行 Start-Sleep

我有一个名为 XLStoXLSX.ps1 的文件，我正在使用任务计划程序中的 (.bat) 文件运行它，如下所示：

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -File "E:\Conversion XLS to XLSX\XLStoXLSX.ps1"  1>E:\Conversion XLS to XLSX\logs\Logs.log 2>&1

如果我在任务计划程序之外运行它，它将正常工作，这是 XLStoXLSX.ps1 的内容

$folderpath         = "E:\One drive\HAJJAJ\SFTP\data\02_Hour\"
$ConversionFolder   = "E:\One drive\HAJJAJ\Conversion_Space\02_Hour\"
$ProductionFolder   = "E:\One drive\HAJJAJ\SFTP\data\02_Hour\"

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlOpenXMLWorkbook
write-host $xlFixedFormat
$excel = New-Object -ComObject excel.application
$excel.visible = $true  
$filetype ="*xls"
Get-ChildItem -Path $folderpath -Include $filetype -recurse | 
ForEach-Object `
{
    $path = ($_.fullname).substring(0, ($_.FullName).lastindexOf("."))
    $pathBackup = ($_.fullname).substring(0, ($_.FullName).lastindexOf("\"))
    $FileNameToMove = $path.Replace($folderpath,$ConversionFolder)
    $FileNameBackUp = $pathBackup.Replace($folderpath,$ConversionFolder)+"\Done\" 
    $ProductionFileNameToMove = $path.Replace($folderpath,$ProductionFolder)
    $ProductionFileNameBackUp = $pathBackup.Replace($folderpath,$ProductionFolder)+"\Done\"
   
    "Converting $path"
    $FileNameBackUp += $(get-date -f yyyyMMddHHmmss) +  ($_.Name -replace '\.xls', '.xlsx')
    $ProductionFileNameBackUp  += $(get-date -f yyyyMMddHHmmss) +  ($_.Name -replace '\.xls', '.xlsx')

    $workbook = $excel.workbooks.open($_.fullname)
    

    if($_.length/1MB -gt 50MB) {  Start-Sleep -Seconds 400 }else { Start-Sleep -Seconds 20 }
 
    $FileNameToMove += ".xlsx"
    $ProductionFileNameToMove += ".xlsx"
    #moving file to backup file on Conversion_Space before  Conversion
    Move-Item   -Path $FileNameToMove  -Destination  $FileNameBackUp

    $workbook.saveas($FileNameToMove, $xlFixedFormat) 
    $workbook.close()

    
    if($_.length/1MB -gt 50MB)
    { Start-Sleep -Seconds 50 }else { Start-Sleep -Seconds 20 }
    $oldFolder = $path.substring(0, $path.lastIndexOf("\")) 
    
    write-host $oldFolder
    if(-not (test-path $oldFolder))
    {
        new-item $oldFolder -type directory
    }
    
    move-item $_.fullname $oldFolder

    #moving file to backup file on Conversion_Space before  Conversion
    Move-Item   -Path $ProductionFileNameToMove  -Destination  $ProductionFileNameBackUp
    Move-Item   -Path $FileNameToMove  -Destination  $ProductionFileNameToMove
    #moving the file to Production 
    
}
$excel.Quit()
$excel = $null
[gc]::collect()

，代码会被执行，但是不会执行这部分代码：

if($_.length/1MB -gt 50MB) {  Start-Sleep -Seconds 400 }else { Start-Sleep -Seconds 20 }

有什么建议可以解决这个问题，或者是否有更好的方法来给 Excel 文件一些时间来打开？

相关内容