Monday, September 22, 2008

Excel Error "COMExceoption was unhandled" Old format or invalid type library.

When I try to open an Excel Workbook (C# and Excel 2003) using the following statement an error occurs.
oBooks.Open(path, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,0,true);


Error is :

COMException was unhandled
Old format or invalid type library. (Exception from HRESULT: 0x80028018(TYPE_E_INVDATAREAD))


Solution
This is actually an error caused by the Excel. This occurs when you try to automate excel. In your machine the excel installed is english version and the current locale is set to some other language, in my case it was Japanese. The solution is to set the current culture of the thread to english "en-US" before calling this method. After calling this set culture back to culture you need.

Sample
public static void OpenExcelFile()

{
Excel.Application oExcel;
Excel.Workbooks oBooks;
Excel.Workbook oBook;
Excel.Sheets oSheets;
Excel.Worksheet oSheet;
Excel.Range oCells;
Object objMissing = System.Reflection.Missing.Value;
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");oExcel = new Excel.Application();
oBooks = oExcel.Workbooks;
string path = @"c:\test.xls";
//string path = "c:\\test.xls";
//Excel 2000
//oBooks.Open(path, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing,
objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
//Excel 2003
oBooks.Open(path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0,
true);
oExcel.Visible = true;
oBook = oBooks.get_Item(1);
oSheets = oBook.Worksheets;
oSheet = (Excel.Worksheet)oSheets.get_Item(1);
oSheet.Activate();System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
}