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

microsoft excelpowershellscheduled-taskwindows-server-2008

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.

Best Answer

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?

Related Topic