I have a handful of PowerShell scripts that ran as Scheduled Tasks on an old Windows 2008 SP2 server, which output reports as Excel workbooks with multiple worksheets, via a COM object. After migrating these scripts to a new Windows 2012 R2 server, these scripts no longer function. The Server 2008 system was running Office 2007, and the new Server 2012 R2 server has Office 2013 installed.

What is everyone using on their Server 2012 R2 systems to automate generation of multi-sheet reports in Excel via PowerShell and Scheduled Tasks?

I know that Microsoft has said that automating Office applications server-side in non-interactive mode, but it has worked in the past, until now. (https://support.microsoft.com/kb/257757)

I've tried the OpenXML PowerTools for PowerShell (https://powertools.codeplex.com/), but Export-OpenXMLSpreadsheet will only export a single worksheet, and they don't support adding worksheets to existing files (am I missing something?). I understand the OpenXML PowerTools can be extended in C#, but I don't C#. The PowerShell cmdlets were released in Jan 2012, but don't look to have been updated since then (updates have been the core C# code).

I've seen recommendations to add C:\Windows\(System32 or SysWOW64)\config\systemprofile\Desktop, but this hasn't worked on Server 2012.

Has anyone been able to get Excel 2013 to cooperate on Server 2012 R2, or come up with a suitable alternative? I'd rather not start running these scripts from my workstation.

EDIT:

I'm not necessarily looking for an Excel answer, since there are OpenXML ways of doing things now without the Excel application.

Here are some of the errors I'm getting when approaching this from different directions. When I use:

$excel = New-Object -comobject Excel.Application
$workbook = $excel.Workbooks.Add() 

Results in error:

Exception calling "Add" with "0" argument(s): "Microsoft Excel cannot open or 
save any more documents because there is not enough available memory or disk 
space. 
 To make more memory available, close workbooks or programs you no longer 
need. 
 To free disk space, delete files you no longer need from the disk you are 
saving to."
At C:\path\to\script.ps1:21 char:2
+     $workbook = $excel.Workbooks.Add()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

The server is definitely not lacking for resources.

I can copy a blank XLSX file and use .Open($xlFile) instead of .Add(), but then when I go to open a CSV file to copy/paste the content to a worksheet, I get this one:

Exception calling "Open" with "1" argument(s): "The server threw an exception. 
(Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"
At C:\path\to\script.ps1:65 char:2
+     $tempcsv = $excel.Workbooks.Open($CSVFile)
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

Things that I've tried so far, with no success:

• I created C:\Windows\System32\config\systemprofile\Desktop and C:\Windows\SysWOW64\config\systemprofile\Desktop as suggested here: http://social.technet.microsoft.com/Forums/windowsserver/en-US/0751119d-84d5-4a77-8240-1c4802f97375/powershell-scheduled-tasks-wont-start-excel?forum=winserverpowershell
• Add Local Launch and Local Activation permissions to the Microsoft Excel Application DCOM Config, Grant "Read & Execute, List folder contents, Read" permissions on the \config\systemprofile\Desktop folder. Grant "Modify, Read & Execute, List Folder Content, Read, Write" permissions for the account on the following folders:
  \config\systemprofile\AppData\Roaming\Microsoft
  \config\systemprofile\AppData\Local\Microsoft
  as suggested here: http://social.technet.microsoft.com/Forums/windowsserver/en-US/aede572b-4c1f-4729-bc9d-899fed5fad02/run-powershell-script-as-scheduled-task-that-uses-excel-com-object?forum=winserverpowershell

@ jrv - I will try posting a similar question in the Excel 2013 forum, but since I'm looking for a scripting solution to produce an OpenXML spreadsheet, and not something that necessarily uses the Excel application (though such a solution would not be turned away), I thought this the more appropriate venue.

(to reiterate from OP) I have seen and acknowledge Microsoft's statements regarding the unsupported nature of automating Office applications in non-interactive environments, but the truth of the matter is, supported or not, it worked in the past (Server 2008), it's what I inherited from my predecessor, and I know I'm not the only one who has been using Excel in this way. I'm only asking fellow scripters, some of whom must also be using Excel in this "unsupported" fashion, how they are automating creation of their spreadsheet reports after moving to Server 2012. A different system modification to make the Excel comObject continue working as before? Direct manipulation of the OpenXML document? Other solutions that may or may not require launching the Excel application?

• Edited by CaffeineComa Thursday, August 28, 2014 2:43 AM
• Changed type Bill_StewartModerator Friday, October 31, 2014 4:54 PM Discussion

#1 - Ask in Excel 2013 forum.
#2 - Microsoft has repeatedlynoted that this is NOT a supported configuration for Office products.
#3 - What errors are you getting?
#4 - What have you done to debug this:
#5 - No one can be of much help unless you post a very simple example of how this fails.

Hello,

I have the exact issue what you posted above, and tried the all the steps mentioned above. Did you find any solution for it??

Same issue. Have you any solution? thx