Powershell script stops working when ran through task scheduler

Question

I am running the script below as a scheduled task with the user logged on the server. It converts an xls file to csv using the Excel.Application COM object. The conversion works, but eventually breaks and I don't know why.

I have the task run the following command which should in theory allow it to run constantly:

powershell.exe -noexit -file "filename.ps1"

Any thoughts on what to try?

$server = "\\server"
$xls = "\path\XLS\"
$csv = "\path\CSV\"
$folder = $server + $xls
$destination = $server + $csv

$filter = "*.xls" # <-- set this according to your requirements

$fsw = New-Object IO.FileSystemWatcher $folder, $filter -Property @{
    IncludeSubdirectories = $true # <-- set this according to your requirements
    NotifyFilter = [IO.NotifyFilters]"FileName, LastWrite"
}

Register-ObjectEvent $fsw Created -SourceIdentifier FileCreated -Action {

    $path = $Event.SourceEventArgs.FullPath
    $name = $Event.SourceEventArgs.Name
    $changeType = $Event.SourceEventArgs.ChangeType
    $timeStamp = $Event.TimeGenerated

    $excelFile = $folder + $name
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false
    $wb = $E.Workbooks.Open($excelFile)

    foreach ($ws in $wb.Worksheets) {
        $n = "output_" + $name -replace ".XLS"
        $ws.SaveAs($destination + $n + ".csv", 6)
    }

    $E.Quit()     
}

Are you running the task as a user who has the rights to access the network server? — Jeff Zeitlin
– Jeff Zeitlin, Commented Jun 30, 2017 at 17:02
"The converting works, but eventually breaks and I don't know why." Can you add more detail? Which part works, and which part breaks? Does it work once and then fails 2nd time or works a few times and then breaks? Does it matter whether the user is logged in or not? Task screenshots/settings would be helpful — G42
– G42, Commented Jun 30, 2017 at 17:03
As Jeff said, verify the user the task is running as. There is a field in scheduled tasks that allows you to enter a user and password to run as. If it "partially" runs, it may be that it doesn't have access to a certain directory you are trying to convert — Kellen Stuart
– Kellen Stuart, Commented Jun 30, 2017 at 17:22
@gms0ulman - converting happens. i drop a .xls file into the folder, it gets converted to .csv because the script is running in the background. i just notice eventually usually 4-5 days later, when i drop a .xls file, the convert stops even though the powershell script is still running. — user2600407
– user2600407, Commented Jun 30, 2017 at 17:25
I guess you should have your script increment a log file to know which part fails? — sodawillow
– sodawillow, Commented Jun 30, 2017 at 17:51

ShanayL · Accepted Answer · 2017-07-21 15:09:43Z

1

I was doing something similar with word. I couldnt use Quit alone. I think using Quit hides Excel. Try releasing the com object by using:

$E.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($E)
Remove-Variable E

i dont know if you are opening the Excel application but if you are you can maybe use

$wb.close($false)

Let me know if it works...

Ref: https://technet.microsoft.com/en-us/library/ff730962.aspx?f=255&MSPPError=-2147217396

answered Jul 21, 2017 at 15:09

ShanayL

1,2572 gold badges14 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell script stops working when ran through task scheduler

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related