Running excel from a windows service

Microsoft washes its hands off server side office automation claiming that office products are not meant for unattended non-interactive use. But office automation is cruel reality that companies have to live with. So what are we waiting for. Create a windows service, add a reference to Microsoft.Office.Interop.Excel and write code written in the windows service to open an excel file and run a macro.

Sample Source Code:

public static void RunMacro(string filePath, params object[] parameters)
        {
            //Open the file and run the macro.
            Excel.Application oExcel = null;
            Excel.Workbooks oBooks = null;
            Excel._Workbook oBook = null;
            object oMissing = Type.Missing;
            Logger.Debug(string.Format(“Opening file {0} to run macro”, filePath));
            try
            {
                oExcel = new Excel.Application();
                oExcel.Visible = false;
                oBooks = oExcel.Workbooks;

                oBook = oBooks.Open(filePath, oMissing, oMissing,
                    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

                // Run the macros.
                RunMacro(oExcel, parameters);
                Logger.Debug(string.Format(“Run macro successful for file {0}”, filePath));
            }
            finally
            {
                if (oBook != null)
                {
                    oBook.Close(false, oMissing, oMissing);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
                    oBook = null;
                }

                if (oBooks != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
                    oBooks = null;
                }

                if (oExcel != null)
                {
                    oExcel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
                    oExcel = null;
                }
            }
        }

Exceptions galore:

Fire up and code in debug mode and it works fine. Deploy the windows service and  you get all sorts of exceptions. Here’s one.

SOURCE: Microsoft Office Excel

MESSAGE: Microsoft Office Excel cannot access the file ‘C:\MyExcelFile.xls’. There are several possible reasons:

• The file name or path does not exist.

• The file is being used by another program.

• The workbook you are trying to save has the same name as a currently open

Clearly none of the above is the reason for the exception. Thanks Microsoft. Very helpful.

Steps to resolve most server side excel automation issues.

1.    Run the windows service as a user who has permissions on excel. Windows services generally run using the local account and these are mostly not configured to open excel. Double click on your windows service in services.msc -> Choose Log on Tab -> Enter interactive user account and password.

2.    Excel looks for certain folders in the system.  A Desktop folder seems to be necessary in the systemprofile folder to open file by Excel. A lot of times, this solves a lot of excel automation issues.

• For 64-bit (x64), create this folder: C:\Windows\SysWOW64\config\systemprofile\Desktop.
• For 32-bit (x86), create this folder: C:\Windows\System32\config\systemprofile\Desktop

3.    Also DCOMConfig settings need to be changed. Follow the following steps to change DCOMConfig settings for excel.

• Start -> dcomcnfg.exe (you might be prompted for administrator credentials) -> Console Root -> Component Services -> My Computer -> DCOM Config
• Click “Microsoft Excel Application”. Right Click -> Properties.
• Choose Identity tab. Select Interactive User.
• Choose Security Tab. Customize “Launch and Activation Permissions”. Edit and add permissions to the user whom you are running the windows service.
• Customize “Access Permissions”. Edit and add permissions to the user whom you are running the windows service.

Refer to the following site for details for more details on how to change the dcom config settings: http://forums.asp.net/t/1303594.aspx

4.    Follow the following steps if Microsoft Excel Application is not visible under dcomcnfg settings. On 64 bit system with 32 bit Office –

• Start
• Run
• mmc -32
• File
• Add Remove Snap-in
• Component Services
• Add
• OK
• Console Root
• Component Services
• Computers
• My Computer
• DCOM Config
• Microsoft Excel Application. Now perform the steps mentioned in previous step.

Apart from these steps, I have in extreme cases ended up modifying a few registry entries. But the above steps resolve most of the issues. Hope this article helps you to fix server side office automation deployment issues allowing you focus on real work!