Home / server / Questions / 584746
In Process
Michael Cornn
Asked: 2014-03-27 11:15:05 +0800 CST

PowerShell script can't open a file when run as a scheduled task

  • 772

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?

powershell

2 Answers

  1. 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.)

    • 1

  2. Take a look here:

    https://stackoverflow.com/questions/7106381/microsoft-office-excel-cannot-access-the-file-c-inetpub-wwwroot-timesheet-app

    You just have to do this:

    1. Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop " (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop " (for 32 bit Windows)
    2. Set Full control permissions for directory Desktop (for example in Win7 & IIS 7 & DefaultAppPool set permissions for user "IIS AppPool\DefaultAppPool")

    It worked for me! Hope it helps

    • 0