18

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 with no 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 with no 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 $spreadsheet
$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 with no 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?

Matt
  • 45,022
  • 8
  • 78
  • 119
Bagheera
  • 1,358
  • 4
  • 22
  • 35

5 Answers5

32

I think you've hit a bug in Excel:

You have to create a folder (or two on a 64bit-windows):

(32Bit, always)

C:\Windows\System32\config\systemprofile\Desktop

(64Bit)

C:\Windows\SysWOW64\config\systemprofile\Desktop

I have had the same problem and this was the only solution i have found.

From TechNet Forums (via PowerShell and Excel Issue when Automating )

Community
  • 1
  • 1
TessellatingHeckler
  • 27,511
  • 4
  • 48
  • 87
  • 1
    This worked for MSWord and Visio automation upon encountering the same problem, too. – CJBS Dec 23 '16 at 00:15
  • 1
    Additionally, you might also need to grant the account permissions to the newly created Desktop folders. – John G Hohengarten May 10 '19 at 18:05
  • Careful when using code from the linked sources, they have the required folder named "Dektop"(vs "Desktop"), adding a folder named "Dektop" doesn't fix anything... – David Rogers Jan 07 '20 at 17:11
1

The solutions above didn't work in my SCSM 2012 Scorch environment, instead I used PSExcel (https://github.com/RamblingCookieMonster/PSExcel) which has no dependency on having Excel installed or the ComObject.

lovedatsnow
  • 59
  • 6
1

To extend what @TessellatingHeckler provided, you can run the following commands in Powershell(As Admin/Elevated) to create the folders before opening excel, in my script this fixed the issue:

New-Item -ItemType Directory -Force -Path C:\Windows\System32\config\systemprofile\Desktop
if ([Environment]::Is64BitProcess -ne [Environment]::Is64BitOperatingSystem)
{
    New-Item -ItemType Directory -Force -Path C:\Windows\SysWOW64\config\systemprofile\Desktop
}
David Rogers
  • 2,601
  • 4
  • 39
  • 84
1

I had to set my Scheduled Task to run 'only when user is logged on' (logged on to server as the service account that runs task then disconnect session) as it seems to be a limitation with the Task Scheduler and Excel. It's a pretty lame workaround but it works.

Alex Insites
  • 11
  • 1
0

To reiterate what TessellatingHeckler said. I had to resolve this issue on a 64 bit system, so I used the following command which made the PowerShell script finally work via Task Scheduler:

New-Item -ItemType Directory -Force -Path C:\Windows\SysWOW64\config\systemprofile\Desktop
Fedor
  • 17,146
  • 13
  • 40
  • 131
Nakuna
  • 1
  • 1
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/33576613) – Daniel Mann Jan 10 '23 at 06:45
  • This was marked as not providing an answer to the question, and was crossed out. It was replaced by literally the exact same thing. I copy/pasted the line I use in my PowerShell script to make my script work in my answer. I do not understand why my answer was marked as a non-answer, then replaced by you with precisely the same thing. – Nakuna Jan 11 '23 at 22:12