I created one task with high privileges
C:WindowsSystem32WindowsPowerShellv1.0powershell.exe -ExecutionPolicy Bypass -File "D:OneDriveOneDrive - HAJJAJSCRIPTS 1_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:OneDriveOneDrive - HAJJAJFTPDataStock_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:OneDriveOneDrive - HAJJAJProductionStock_From_ERP"
# Set the path to the archive directory
$archiveDirectory = "D:Archive"
# Set the path to the error log file
$errorLogFilePath = "D:LogsConversionErrorLog_$(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:OneDriveOneDrive –
HAJJAJFTPDataStock_From_ERPINVIRSIQNZ_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.