PowerShell script can't open a file when run as a scheduled task
Add the -No Profile switch to powershell.exe in your scheduled task (edit your task, go to the Actions tab, edit your action and in Add arguments field, add -No Profile as the first argument.)
Related videos on Youtube
Michael Cornn
Updated on September 18, 2022Comments
-
Michael Cornn over 1 year
My PowerShell (2.0) script has the following code snippet:
$fileName = "c:\reports\1.xlsx" $xl = new-object -comobject excel.application $xlFormat = [Microsoft.Office.Interop.excel.XlFileFormat]::xlWorkbookDefault $xl.displayalerts = $false $workbook = $xl.workbooks.open($fileName) #Code to manipulate a worksheet $workbook.SaveAs($fileName, $xlformat) $xl.quit() $error | out-file c:\reports\error.txt
I can run this script in the PowerShell command prompt without any issues. The spreadsheet gets updated, and error.txt is empty. However, when I run it as a task in Task Scheduler, I get errors with the first line.
Exception calling "Open" with "1" argument(s): "Microsoft Office Excel cannot access the file 'C:\reports\1.xlsx'. 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.
I run the task with the same credentials I use to run the script in the PowerShell command prompt. When I run the script manually, it can open, update, and save the spreadsheet without any issues. When I run it in Task Scheduler, it can't access the spreadsheet.
The file in question is readable/writeable for all users. I've verified I can open the file in Excel with the same credentials. If I make a new spreadsheet and put its name in as the $filename, I get the same results. I've verified that there are no instances of Excel.exe in Task Manager.
Oddly, if I use Get-Content, I don't have any problems. Also, if I make a new spreadsheet, I don't have any problem.
$fileName = "c:\reports\1.xlsx" $xl = get-content $filename $xl = new-object -comobject excel.application $xlFormat = [Microsoft.Office.Interop.excel.XlFileFormat]::xlWorkbookDefault $xl.displayalerts = $false # Commented out $workbook = $xl.workbooks.open($fileName) $workbook = $xl.workbooks.add() #Code to manipulate a worksheet $workbook.SaveAs($fileName, $xlformat) $xl.quit() $error | out-file c:\reports\error.txt
That works fine. So Get-ChildItem can open the file without any issue. ComObject can open the file if I run it manually, but not if it's run as task.
I'm at a loss. Any ideas?
-
Zoredache about 10 yearsSpend some time adding some more error checking to your script. What What happens if you
test-path $fileName
? What happens if you `Test-Path "c:\reports". Also, are you sure sure the file isn't open by another program? -
Michael Cornn about 10 yearsThat was the point of adding "Get-Content $filename". BTW, I had a typo; $spreadsheet was supposed to be $filename. Anywat, if Get-Content succeeded (and it did), that indicated that Powershell could reach and read the file. In any event, I went ahead and add "test-path c:\reports" to the script. That command completed with no errors.
-
HopelessN00b about 10 yearsDoes taking the quotes out of the filename help? (
$fileName = c:\reports\1.xlsx
)
-
-
john about 10 yearsCare to expand?
-
Gomibushi almost 8 yearsAlways run scheduled scripts with -Noprofile, check this for completeness msdn.microsoft.com/en-us/powershell/scripting/core-powershell/… - oh, and triple check that it runs with the credentials you want it to run under. Log in with those and test or use RunAs. It's often the simple things. :)