0

I created one task with high privileges

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

the script .ps1 used to convert file from xls to xlsx:

# 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."

when I run this .bat file in normal mode (even with out run as admin) it execute code with no issues, but when I run it using Task Scheduler I am getting the below error:

Microsoft Excel cannot access the file 'D:\OneDrive\OneDrive - HAJJAJ\FTPData\Stock_From_ERP\INVIRSIQNZ_528800221_1.xls'. 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.

HAJJAJ
  • 121
  • 4

0 Answers0