0

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?

  • Spend 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? – Zoredache Mar 26 '14 at 19:20
  • That 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. – Michael Cornn Mar 26 '14 at 19:25
  • Does taking the quotes out of the filename help? ($fileName = c:\reports\1.xlsx) – HopelessN00b Mar 26 '14 at 19:55
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.)

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.