일부 오류가 있는 PowerShell 스크립트를 실행하는 작업 스케줄러

Question

높은 권한으로 작업을 하나 만들었습니다.

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Bypass -File "D:\OneDrive\OneDrive - HAJJAJ\SCRIPTS\01_Hourl_Stock_From_ERP.ps1"

파일을 xls에서 xlsx로 변환하는 데 사용되는 스크립트 .ps1:

# Set the path to the directory containing the Excel files
$sourceDirectory = "D:\OneDrive\OneDrive - HAJJAJ\FTPData\Stock_From_ERP"

# Set the path to the conversion directory
$conversionDirectory = "D:\ConversionZone"

# Set the path to the destination directory where you want to move the converted files
$destinationDirectory = "D:\OneDrive\OneDrive - HAJJAJ\Production\Stock_From_ERP"

# Set the path to the archive directory
$archiveDirectory = "D:\Archive"

# Set the path to the error log file
$errorLogFilePath = "D:\Logs\ConversionErrorLog_$(Get-Date -Format 'yyyyMMdd_HHmmss').txt"

# Set the new filename
$newFilename = "Stock_From_ERP.xlsx"

# Set the new sheet name
$newSheetName = "Stock_From_ERP_Sheet"

# Get a list of all .xls files in the source directory
$xlsFiles = Get-ChildItem -Path $sourceDirectory -Filter *.xls | Sort-Object CreationTime -Descending | Select-Object -First 1

# Loop through each .xls file
foreach ($file in $xlsFiles) {
    # Construct the full path to the source file
    $sourceFilePath = $file.FullName

    # Construct the full path to the conversion file
    $conversionFilePath = Join-Path -Path $conversionDirectory -ChildPath $newFilename

    try {
        # Create a new Excel Application object
        $excel = New-Object -ComObject Excel.Application

        # Disable alerts and set visible to false to prevent Excel from displaying prompts
        $excel.DisplayAlerts = $false
        $excel.Visible = $true

        # Open the problematic file
        $workbook = $excel.Workbooks.Open($sourceFilePath)

        # Change the sheet name
        $worksheet = $workbook.Worksheets.Item(1)
        $worksheet.Name = $newSheetName

        # Save the workbook as .xlsx format in the conversion directory
        $workbook.SaveAs($conversionFilePath, 51)  # 51 represents the XLSX file format

        # Close the workbook and Excel application
        $workbook.Close()
        $excel.Quit()

        # Release the COM objects
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
        [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
        Remove-Variable excel -ErrorAction SilentlyContinue

        # Copy the converted file to the destination directory with preserving permissions
        Copy-Item -Path $conversionFilePath -Destination $destinationDirectory -Force

        # Remove the original file
        Remove-Item -Path $sourceFilePath -Force

        # Add a waiting time of 5 seconds before moving the source file to the archive directory
        Start-Sleep -Seconds 50

        # Move the source file to the archive directory
        Move-Item -Path $conversionFilePath -Destination $archiveDirectory -Force
    } catch {
        # Log the error to the error log file
        $_.Exception.Message | Out-File -FilePath $errorLogFilePath -Append

        Write-Host "Error processing $($file.Name): $_"
        continue  # Skip processing this file and continue with the next one
    }
}

Write-Host "Conversion and move complete."

이 .bat 파일을 일반 모드(관리자 권한으로 실행하지 않은 경우에도)에서 실행하면 문제 없이 코드가 실행되지만 작업 스케줄러를 사용하여 실행하면 아래 오류가 발생합니다.

Microsoft Excel에서 'D:\OneDrive\OneDrive - HAJJAJ\FTPData\Stock_From_ERP\INVIRSIQNZ_528800221_1.xls' 파일에 액세스할 수 없습니다. 여러 가지 가능한 이유가 있습니다:

• 파일 이름이나 경로가 존재하지 않습니다. • 다른 프로그램에서 해당 파일을 사용하고 있습니다. • 저장하려는 통합 문서의 이름이 현재 열려 있는 통합 문서와 동일합니다.