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

Question

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

Comments

[gyzpunk]

Did you solve your problem ? <br> I have the same here with VBS, and this is really anoying... Manipulating Excel workbook is working great but saving it fails !

[Daniel Richnak]

I'll give this article a read. At first glance it shouldn't apply because it's talking about webapp or Win service code, or scheduled tasks running as SYSTEM etc. I'm trying to run as (my) domain account... Also, my hope is to run it as a service account without logon needed, but my initial tests it's running as my account with that option, while I'm logged in. So even if I'm interactive on the box, with that option it's failing.

[Ben Pilbrow]

@riknik - It applies to any kind of office automation without a user profile loaded. Office expects certain things to exist, and not all of them are loaded when run as a scheduled task, whether it is your account or the SYSTEM account.

[Daniel Richnak]

:( Seems you're probably right. However, also seems that this method used to work on an older version of Windows/Excel, based on this 2006 scripting guys post: blogs.technet.com/b/heyscriptingguy/archive/2006/05/18/… ... Will keep Question open for a while to see if anyone has a workaround. If this can't be done, will involve a rewrite of a bunch of code; will end up w/much cleaner solution, but a lot of work. Thanks for your help.

[Flintlock Wood]

The solution by SiteMonger worked. In case someone got as confused as I did, you just have to create the folder below and do nothing else ...and my Excel files get generated by my task scheduler application! (32Bit) C:\Windows\System32\config\systemprofile\Desktop (64Bit) C:\Windows\SysWOW64\config\systemprofile\Desktop

[Registered User]

There is a typo above for 32-bit. Dektop. Should be Desktop.