Powershell script cannot access a file when run as a Scheduled Task

Question

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?

Kiser

This problem also arises when running via aws ssm send-command – Interpretive

Did you fix this issue? For me , excel sign-in window pops up every single time. – Patty

Answer 1

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 )

Fastening

This worked for MSWord and Visio automation upon encountering the same problem, too. – Alten

Additionally, you might also need to grant the account permissions to the newly created Desktop folders. – October

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... – Serrulation

Answer 2

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.

Truckload

Answer 3

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
}

Serrulation

Answer 4

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.

Thereabouts

Answer 5

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

Barrios

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Sapphirine

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. – Barrios