Exception When Opening Excel File in C# Using Interop

Question

I have a program running on a server which downloads an .xls file from a website every day, opens it then converts it to a .csv to be able to parse it correctly and add to a database.

When I run it on my local PC everything works fine and it works most of the time on the server as well, but maybe once or twice a week I get this exception:

Exception occurred: System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)

Here's a code snippet:

 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wbWorkbook = app.Workbooks.Open(_dir + _fileNameTrim + ".xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            wbWorkbook.SaveAs(_dir + _fileNameTrim + ".csv", Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            wbWorkbook.Close(false, "", true);

I've read many things online, a lot of solutions mention security settings but I don't think this is the case with me, otherwise it wouldnt work at all. People have also mentioned the file being corrupt. I'm really not sure. Any help would be much appreciated.

UPDATE:

Excel is installed on the server and the exception occurs when trying to open the file.

Solution 1

Run> dcomcnfg

This will open Component Services and navigate to Console Root \Component Services\Computers\My Computer\DCOM Config\Microsoft Excel Application Right click Microsoft Excel Application and select Properties

Click on Identity Tab and check selection.

It works for me when select option - The interactive user

Please try, best luck!!

Solution 2

.Workbooks.Open(strPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlCorruptLoad.xlExtractData);

Solution 3

I have the same problem. It is specifically related to new Microsoft.Office.Interop.Excel.Application(); and what I have been doing is to create the file in a temp location where I know I don't have any permission issues. Once I am done creating the document, I then move it to the final destination, deleting the file created in my temp location.

So far I have not had any issues.

I suggest you create a log file and save it by date where it can log the steps it completes throughout your code. This way you can see where it fails.

If possible too, do a Process.Kill() on excel if this is the only application that requires excel. This helps if Excel.exe does not close properly.

Final, as stated in the comments above, Excel automation on the server is not supported. I would recommend looking in to SSIS for automation of excel.

UPDATE:

Make sure you test if the application runs even when no user is logged on. You might need to set the DCOM identity for Microsoft Excel to run as a specific user account and not the interactive user.

Go to:

Control Panel\All Control Panel Items\Administrative Tools

Click on

Component Services and navigate to Console Root\Component Services\Computers\My Computer\DCOM Config\Microsoft Excel Application

Right click Microsoft Excel Application and select Properties

Under the Security tab, set to Customize on all and give access to Everyone (for testing)

Under the Identity tab, set This User and select the appropriate account (same account as the Task Scheduler is using to run your application)

Good Luck!

Comments

[Toz]

I doubt it is a permission issue, as it works most of the time.

[hulzi]

Maybe you have a problem with your sheet (Autofilter, hidden names in your sheet). With Microsoft.Office.Interop.Excel.XlCorruptLoad.xlExtractData you can ignore this cause of defect (Only read).

[Remy]

He says it occurs when opening the excel file. I assume that he means that he is doing it via code either at the time of creating the object or when using the .Open() method. Every once in a blue moon, could indicate that the application works, so any change has to be related to the server. Changing the DCOM Identity could be a good place to start with...

[user1108069]

@Harry , maybe you can refer to stackoverflow.com/questions/1699936/…

[user1108069]

@Harry, actually I mean, you can refer to the first answer to that question, it explains the error, and the parameter in Open method, which resolve you question. :)

[Harry]

@user1108069 You are mistaken buddy. I never had a question here. My comment was from the review queue.

[user1108069]

@Harry, well... I'm confused,..what is a review queue? -_-

[Cătălin Rădoi]

They should put a heart icon on stackoverflow!