When ran as a scheduled task, cannot save an Excel workbook when using Excel.Application COM object in PowerShell

I'm having an issue where I've automated creating an Excel.Application COM object, add some data into a workbook, and then saving the document as an xlsx.

This works fine if:

• I'm already in Powershell interactive host and either run each command in sequence, or execute as a ps1.
• I run it from cmd.exe, using the syntax: powershell.exe -command "c:\path\to\powershellscript.ps1"
• I create a scheduled task in Windows 7 / Server 2008 R2, use the above powershell.exe -command syntax, and use the mode "Run only when the user is logged on".

It fails when I modify the same scheduled task, but set it to "run whether the user is logged on or not".

Here's a sample script that illustrates the problem I'm having:

$Excel = New-Object -Com Excel.Application
$Excelworkbook = $Excel.Workbooks.Add()
$excelworkbook.saveas("C:\temp\test.xlsx")
$excelworkbook.close()

I have a theory that the COM object fails somehow if my profile isn't loaded / if it's not performed in a command window.

Any ideas on which options to choose when creating the scheduled task, or which options to use when creating the Excel object or using the SaveAs() function? Can anybody reproduce this? I've been able to see this behavior on both a Server 2008 R2 machine, and Windows 7. Haven't tried other platforms.

Solution 1:

Are you trying to actually run this when you're not logged on? If so, I'd point you at this Microsoft KB article for some likely causes: Considerations for server-side Automation of Office. The title says server-side but the article also specifies this applies to client versions of Windows not running in the interactive session with a loaded user profile.

If you do want this to run this without a user being logged on (with the option you describe, it sounds like you do), ultimately I'm not sure you're going to be able to fix your problem for the reasons described in the article. If you only need it to run when you're logged on, just don't choose that option?

Solution 2:

I've been burned by this and didn't want to rewrite the code. I saw your post and several others which made me about to give up. However, my persistence paid off. I was trying to have Jenkins run a script to inventory our production environment and output to Excel. I didnt want a text doc because I was highlighting software versions that didnt match in RED, so needed Excel.

Here is the answer that worked for me:

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

(64Bit, always)

C:\Windows\System32\config\systemprofile\Dektop

(32Bit)

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

Link that someone provided as the source:

http://www.patton-tech.com/2012/05/printing-from-scheduled-task-as.html

My source was:

http://social.technet.microsoft.com/Forums/en/winserverpowershell/thread/aede572b-4c1f-4729-bc9d-899fed5fad02