Monday, March 9, 2009

Excel Interop COMException HRESULT 0x800A03EC via C# (.NET 2.0)

Today I'm writing some code to read Excel files via the interop assemblies. I was reusing working code and it suddenly started failing with a COMException which said the error code was HRESULT 0x800A03EC.

I'm using the following code:

string excelLocation = @"C:\test.xls";
Microsoft.Office.Interop.Excel.Application _app = new Microsoft.Office.Interop.Excel.Application();

Workbook wbook = _app.Workbooks.Open(excelLocation,
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, Type.Missing);

Worksheet sheet = (Worksheet)wbook.Sheets[1];

/** rest of the code **/

and I get the COMException in the line where I open the workbook.

So, I open the file via regular Excel and I find out it finds the file somewhat corrupt, Excel tells me it had fixed some font and sheet name problems with the file and that if I want to make that fix permanent to save the file. I save the file and then the code works!

But wait, this is not the proper solution if you want a truly automated processing of the files. Given that I don't control the Excel generation process I cannot fix the corruption on the source side of things, so I'll have to fix it on my side.

I started to dig into the Open method parameters and found that the last parameter is suggestively called 'CorruptLoad' so, the solution:

string excelLocation = @"C:\test.xls";
Microsoft.Office.Interop.Excel.Application _app = new Microsoft.Office.Interop.Excel.Application();

Workbook wbook = _app.Workbooks.Open(excelLocation,
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, true);

Worksheet sheet = (Worksheet)wbook.Sheets[1];

/** rest of the code **/

And that opens it without throwing an exception and gets all the data in the sheets, at least for this particular type of file corruption.

16 comments:

Nikolay said...

Great post! I tried so many different approaches and nothing worked except this. Thanks for sharing your solution.

Nikolay said...

Bad news. This only worked on development (XP)and staging (Windows Server 2003), but still getting it on the production server(Windows Server 2003) when I try to open the file (error 0x800A03EC). I have no idea where to start from. I checked and compared all user permissions on both servers, also checked dconfig and everything matches completely.
Any suggestions will be appreciated.
Thank you.

Alex said...

For work with excel files and similar type files I often use next tool-Excel file corrupted,because it helped myself many times,has free status as far as I remember,and utility can try Excel repairs manually, by retyping all documents, but it is time consuming, you can spend many days for this purpose, when Excel file has corrupted,tool for Excel repairing is very easy to use, when Excel file corrupted, it has only several buttons and functions for Excel document repair: open file, start its analysis, preview of recovered contents and export of recovered data into a new document in Microsoft Excel format,allows to perform all steps and take a look into recovered contents of this file.

Anonymous said...

Hi, found a possible solution here: http://www.made4dotnet.com/Default.aspx?tabid=141&aid=15

Anonymous said...

Hi, found a possible solution here: http://www.made4dotnet.com/Default.aspx?tabid=141&aid=15

Locivars Vonvik said...

Thanks anonymous, the locale issue could very well be the problem nikolay is seeing. I've never seen that problem though.

Nikolay said...

I was sure that it was security, but didn't have the proof. Now I have it. Adding "Network Service" to the Administrators' group fixed my problem. Although I'm not sure why, I'm more than happy that it worked. Initially "Network Service" account had full rights on the production server (Windows Server 2003)on application folder, asp.net folders, etc. but I guess this wasn't enough.
I hope this helps someone.

Rolle said...

Thanks for posting this! Unfortunately it didn't solve it for me, but it must be some kind of security issue as you say. The same code works on my local dev machine, the Dev/Test machine and Production machine. But it does NOT work in Acceptance/Staging machine... Man I have been trying to solve this for too long now... 8-/

Anonymous said...

I was seeing this issue in my excel add-in because customer used their custom standards in regional and language options.

Alex said...

I know a lot of good programs and one of them pleased me. It to my surprise decided all my old problems with damaged excel files. Perhaps it will help in similar conditions, for example in this - recover Excel.

Anonymous said...

Yes, also in my case the presented solution was very helpful, thank You for your help :)

Ando said...

Thanks for your post and for sharing your experience.
I've stumbled into this issue and spent a couple of hours yesterday trying to fix it - without success :(.
Although it now works, I still don't know how exactly does the excel file get corrupted (because it only happens 1 out of 10 times).
Any thoughts on the possible corruption causes?

deveshdedhia said...

thanks a lot. Saved me some time. it would be interesting to see if oledb also has a way to deal with corrupt load.

CDavid said...

Great find, thanks a lot!

Ghislain said...

I ran into this issue. Simply used:
xlWorkBook.Close(True, Filename, False)

instead of :
xlWorkSheet.SaveAs(Filename, 56)

and it worked !

lingmaaki said...

A simple Excel Interop tutorial

http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

ling