Powershell runs differently when run through task scheduler

Question

I can post the code if needed (its rather long) but the short of it is that I have a Ps1 script that gets info about a share (file name, creation date etc) and creates a CSV. file in a location. Then it changes all the csv to xlsx and deletes the csv. When run manually this works perfectly, when scheduled to run through task scheduler with highest permissions it creates the csv, then deletes everything, seeming to skip the convert to xlsx.

What would cause the same script to behave differently when run manually vs. through task scheduler?

CODE BELOW:

    ###Make sure I can Access the Share
net use \\Share\Share /user:USER PASS /persisten:no

###Move the ones that exist to Back-Up Delete Backup
Remove-Item C:\Users\USER\Desktop\OutputBk\* -recurse
Get-ChildItem -Path "C:\Users\USER\Desktop\Output" -Recurse -File | Move-Item -Destination "C:\Users\USER\Desktop\OutputBk"

### Output1

Get-ChildItem -Path \\SHARE\Output1 -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output1.csv -NoTypeInformation

### Output2

Get-ChildItem -Path \\SHARE\Output2 -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output2.csv -NoTypeInformation

### Output3

Get-ChildItem -Path \\SHARE\ Output3-Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output3.csv -NoTypeInformation

### Output4

Get-ChildItem -Path \\SHARE\ Output4 -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output4.csv -NoTypeInformation

### Output5

Get-ChildItem -Path \\SHARE\ Output5 -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output5.csv -NoTypeInformation

### Output6

Get-ChildItem -Path \\SHARE\ Output6 -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output6.csv -NoTypeInformation

### Output7

Get-ChildItem -Path \\SHARE\ Output7 -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output7.csv -NoTypeInformation

### Output8

Get-ChildItem -Path \\SHARE\ Output8 -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output8.csv -NoTypeInformation

### Output9

Get-ChildItem -Path \\SHARE\ Output9 -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output9.csv -NoTypeInformation

### Output10

Get-ChildItem -Path \\SHARE\ Output10 -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output10.csv -NoTypeInformation

### Output11

Get-ChildItem -Path "\\SHARE\Recycle Bin - Output11" -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output11.csv -NoTypeInformation

### Output12

 Get-ChildItem -Path "\\SHARE\Recycle Bin - Output12" -Recurse | Select-Object CreationTime, LastAccessTime, LastWriteTime, Extension, Length, BaseName, PSParentPath, PSChildName, Directory, DirectoryName, DBDateAdded | Export-CSV C:\Users\USER\Desktop\Output\Output12.csv –NoTypeInformation

### Convert the CSV to XLSX

$workingdir = "C:\Users\USER\Desktop\Output\*.csv"
$csv = dir -path $workingdir
foreach($inputCSV in $csv){
$outputXLSX = $inputCSV.DirectoryName + "\" + $inputCSV.Basename + ".xlsx"

#### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application
$excel.DisplayAlerts = $False 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data » From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs($outputXLSX,51)
$excel.Quit()
}
### To exclude an item, use the '-exclude' parameter (wildcards if needed)
remove-item -path $workingdir -exclude *Crab4dq.csv

### Rename Output1

$xlspath = "C:\Users\USER\Desktop\Output\Output1.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "1"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output2

$xlspath = "C:\Users\USER\Desktop\Output\Output2.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "2"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output3

$xlspath = "C:\Users\USER\Desktop\Output\Output3.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "3"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output4

$xlspath = "C:\Users\USER\Desktop\Output\Output4.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "4"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output5

$xlspath = "C:\Users\USER\Desktop\Output\Output5.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = “5"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output6

$xlspath = "C:\Users\USER\Desktop\Output\Output6.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "6"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output7

$xlspath = "C:\Users\USER\Desktop\Output\Output7.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "7"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output8

$xlspath = "C:\Users\USER\Desktop\Output\Output8.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "8"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output9

$xlspath = "C:\Users\USER\Desktop\Output\Output9.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "9"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output10

$xlspath = "C:\Users\USER\Desktop\Output\Output10.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "10"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output11

$xlspath = "C:\Users\USER\Desktop\Output\Output11.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "11"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

### Rename Output12

$xlspath = "C:\Users\USER\Desktop\Output\Output12.xlsx"
$xldoc = new-object -comobject Excel.application
$xldoc.Visible = $false
$xldoc.DisplayAlerts = $false
$workbook = $xldoc.Workbooks.Open($xlspath )
$worksheet = $workbook.worksheets.item(1)
$worksheet.name = "12"
$workbook.Save()
$workbook.Close()
$xldoc.Quit()

Thanks,

Answer 1

Hope you have given an account which have permission to modify the file, some account may only have permission to write not to modify the file in share folder. After giving the account with required privilege, if the problem still exist, then try adding "Start-Sleep -s 60" before the commandlets which deletes the CSV file. Some time delete task may overide the previous task which suppose the change the file extension. Please post the script if none of the above works.

Answer 2

ty gms0ulman -- Thanks to your links, I found the answer.

It's a DCOM permissions issue.

The only way I've found around issues such as this is to set Excel to run as a specific user through DCOM permissions.

Open Component Services (Start -> Run, type in dcomcnfg) Drill down to Component Services -> Computers -> My Computer and click on DCOM Config Right-click on Microsoft Excel Application and choose Properties In the Identity tab select This User and enter the ID and password of an interactive user account (domain or local) and click Ok Keeping it as the interactive user or the launching doesn't work with the task scheduler unfortunately, even when setting the task up to run under an account that has admin access to the machine

Thanks,