I can not run a powershell script through Task Scheduler (when the useris logged on or not)

Question

i have a powershell script used to

1. change date format
2. convert from xlsx to csv

and needs to be automated and run everyday.

The user account used SPO... can run the script manually and it works. the moment its on task scheduler it wont work aprt if the setting is set to "run only when user is logged on". the user SPO... is a domain user and added to the local administrator account of the windows server 2019 and has access to all concerned folders but the task scheduler wont work.

i checked around some forums and they went through cmd to run the script,which i tried but still does not work. Any idea guys

the script is :

Define paths

$originalFolderPath = "E:\folder\Actual\original"

$convertedFolderPath = "E:\folder\Actual\convert"

$csvOutputFolderPath = "E:\folder\Actual\csv_output"

Ensure the folders exist

if (-not (Test-Path -Path $convertedFolderPath)) {

New-Item -ItemType Directory -Path $convertedFolderPath 

}

if (-not (Test-Path -Path $csvOutputFolderPath)) {

New-Item -ItemType Directory -Path $csvOutputFolderPath 

}

Create an Excel Application objectd

$excelApp = New-Object -ComObject Excel.Application

$excelApp.Visible = $false

$excelApp.DisplayAlerts = $false

Step 1: Apply Date Format to Columns with Headers Containing "Date"

$excelFiles = Get-ChildItem -Path $originalFolderPath -Filter *.xlsx

foreach ($file in $excelFiles) {

try { 

    # Open the workbook 

    $workbook = $excelApp.Workbooks.Open($file.FullName) 

    # Loop through each worksheet in the workbook 

    foreach ($sheet in $workbook.Sheets) { 

        $usedRange = $sheet.UsedRange 

        $headerRow = $usedRange.Rows.Item(1) # Assume the first row contains headers 

        # Find columns with headers containing "Date" 

        $dateColumns = @() 

        for ($col = 1; $col -le $usedRange.Columns.Count; $col++) { 

            $headerValue = $headerRow.Columns.Item($col).Value2 

            if ($headerValue -and $headerValue -like "\*date\*") { 

                $dateColumns += $col 

            } 

        } 

        # Format the identified columns as dd-MM-yyyy 

        foreach ($col in $dateColumns) { 

            $sheet.Columns.Item($col).NumberFormat = "dd-MM-yyyy" 

        } 

    } 

    # Save the workbook to the converted folder 

    $convertedFilePath = Join-Path -Path $convertedFolderPath -ChildPath $file.Name 

    $workbook.SaveAs($convertedFilePath) 

    # Close the workbook 

    $workbook.Close() 

} catch { 

    Write-Host "Error processing file $($file.FullName) for date formatting: $\_" 

} 

}

Step 2: Convert Formatted Excel Files to CSV with Comma Delimiter

$convertedFiles = Get-ChildItem -Path $convertedFolderPath -Filter *.xlsx

foreach ($file in $convertedFiles) {

try { 

    # Open the workbook 

    $workbook = $excelApp.Workbooks.Open($file.FullName) 

    # Loop through each worksheet in the workbook 

    foreach ($sheet in $workbook.Sheets) { 

        # Define the CSV output path with the same name as the original .xlsx file 

        $csvFileName = "$($file.BaseName).csv"  # Same base name as original .xlsx file 

        $outputFilePath = Join-Path -Path $csvOutputFolderPath -ChildPath $csvFileName 

        # Save the worksheet as a CSV file with comma delimiter (default) 

        $excelApp.Application.DisplayAlerts = $false 

        $sheet.SaveAs($outputFilePath, 6) # xlCSV = 6 

    } 

    # Close the workbook 

    $workbook.Close() 

    # After saving, replace commas with semicolons using Notepad and PowerShell 

    # Open CSV file in Notepad and replace commas 

    $csvContent = Get-Content -Path $outputFilePath 

    $csvContent = $csvContent -replace ',', ';'  # Replace all commas with semicolons 

    Set-Content -Path $outputFilePath -Value $csvContent 

    # Delete the original formatted .xlsx file 

    Remove-Item -Path $file.FullName -Force 

} catch { 

    Write-Host "Error processing file $($file.FullName) for CSV conversion: $\_" 

} 

}

Quit the Excel application

$excelApp.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelApp) | Out-Null

Remove-Variable excelApp

Write-Host "Process completed. CSV files saved in $csvOutputFolderPath and original files cleaned up."

***Move from Windows / Other/Unknown / Programs***

Answer 1

Hello,

If your Task requires UAC Elevation, select the “Run with highest privileges” option under Security on the General tab.

Check your scripts for environmental issues – when we run a script, we default to the “%SystemRoot%\System32” folder unless specified in the script.

You can also modify your script to add some type of logging to see where it may be failing.

If you are running nested scripts/programs within one script, try breaking them out as multiple Actions – for example:

So, when script1.cmd finishes, script2.cmd will be launched. Then when script2.cmd completes, script3.cmd will run.

I hope this information helps.

Best regards,

Karlie Weng