Automating Excel 2007 on Windows Server 2008 64-bit

Microsoft doesn't support or recommend automation of Excel in an unattended service. Better to use the new OpenXML SDK if you can - seriously! But the spreadsheet may make heavy use of macros, or you might need to save the output into a non OpenXML format, such as PDF.

Anyway, I needed to automate Excel 2007 via COM Interop, from a task run from an unattended Windows Scheduler service account, on a locked down Windows Server 2008 machine (64-bit edition). Note the steps would be similar for an ASP.NET account or Word 2007 or PowerPoint.

It took me a while to research and solve the various issues that arose. With the hope of saving you some time, here are the steps in one place:

• You'll need to initialise Excel's GUI under the service account: Log in under your service account. Load Excel 2007 to process any setup configuration dialogs. Close and reopen Excel to check that these dialogs no longer appear.
• You'll need to create a "Desktop" Folder under the service account (link): Microsoft removed the Desktop folder from the system account under new versions of Windows Server. In order to prevent the resulting error “Microsoft Office Excel cannot access the file”, create the folder “C:\Windows\SysWOW64\config\systemprofile\Desktop”. In addition, grant full your service account full permissions to this folder.  (On 32 bit versions of Windows Server, the required folder is “C:\Windows\System32\config\systemprofile\Desktop”)
• You'll need to enable COM support for Excel (link): Without these permissions, the following error is thrown when instantiating Excel.Application: “Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.”

• Run “dcomcnfg -32” (so as to view 32 bit components, as Excel 2007 is 32-bit).
• Right click Properties on the node “Component Services\Computers\My Computer\DCOM Config\Microsoft Excel Application”.
• Under the “Security” tab, under "Launch and Activation Permissions" select the "Customize" option and click the "Edit" button and grant your service account "Local Launch" and "Local Activation", but leave "Remote Launch" and "Remote Activation" blank.

• Alert Dialogs must be disabled while processing: It's very important that interactive error messages are not displayed during processes. Immediately after instantiating Excel in code, let excelApp.DisplayAlerts = false.
• Ensure the Excel Process is properly closed: If you are automating Excel from .NET, the Excel process (excel.exe) may not close because the Garbage collector won't release it in a timely way. I've found the following code to be reliable (link):

         excelApp.DisplayAlerts = false;
         excelApp.Quit();

         System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
         excelApp = null;
         excelApp = null;
         GC.Collect();
         GC.WaitForPendingFinalizers();
         GC.Collect();
         GC.WaitForPendingFinalizers();

I'm sure this list isn't comprehensive in all cases, so if anyone has corrections / additions for the list above, I'll update it! Good luck.