Thursday 3 February 2011

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.

    8 comments:

    1. Will you please let me know how to fix this error on windows 2003 server?

      ReplyDelete
    2. Hi Venky, I suspect that fewer restrictions apply to Windows 2003, and that it will be easier to automate on that platform than 2008. However I don't have first-hand experience.

      ReplyDelete
    3. Hi, thanks for the tips, just an addition:
      if the application tries to use the function CopyPicture (to get a bmp out of a range of cells), the application pool identity has to be changed to NetworkService. Using the default IIS ApplicationPoolIdentity fails when calling that function.

      ReplyDelete
    4. Hi!

      I'm trying to do the same with Windows Server 2012R2 and Excel 2007 will start up the COM object, but the file does not open Excel. Advise please ...

      ReplyDelete
    5. guys who need cheap windows can click here, my windows got here adn works well, sharing with you www.aakeys.com

      ReplyDelete
    6. http://goo.gl/xjU3jn

      As far as I know, you can buy it from the authorized vendor online http://goo.gl/xjU3jn . You can count on them 100% as this site is the partner vendor of Microsoft. This is the biggest competitive point.

      http://goo.gl/xjU3jn

      ReplyDelete