Excel Automation: the CultureInfo bug

July 27, 2008 in .NET | 7 comments

If you’re doing some Excel automation like creating a sheet with some graphs from .NET, you may run into the “Old format or invalid type library” error which is quite nicely described in this Microsoft Support article.  At this moment there’s no fix available, but the article describes not one, not two, but three workarounds.  I chose the third one where you switch the Culture for your thread to “en-US”.

When giving this a try, it solved the error mentioned above but then I started getting “Exception from HRESULT: 0x800A03EC” errors.  After searching for a while I finally found the reason: you should not switch the Culture back to the original one after you’ve added your new Excel workbook as shown in the workaround.  First finish whatever you want to automate in Excel and switch the Culture back at the end.

In C# this looks like the following:

   1: // code below assumes the following using statement
   2: // using Excel = Microsoft.Office.Interop.Excel;
   3:
   4: // capture current Culture settings
   5: System.Globalization.CultureInfo systemCultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture;
   6: try
   7: {
   8:     // temporarily change CultureInfo to en-US
   9:     System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
  10:
  11:     Excel.Application excelApplication = new Excel.Application();
  12:     // create new workbook
  13:     Excel._Workbook workbook = (Excel._Workbook)(excelApplication.Workbooks.Add(Missing.Value));
  14:     // get active sheet
  15:     Excel._Worksheet worksheet = (Excel._Worksheet)workbook.ActiveSheet;
  16:
  17:     // create your graphs or whatever you were planning to automate in Excel
  18:
  19:     // give the user control over Excel
  20:     excelApplication.Visible = true;
  21:     excelApplication.UserControl = true;
  22: }
  23: catch (Exception ex)
  24: {
  25:     // handle exception
  26: }
  27: finally
  28: {
  29:     // put CultureInfo back to original
  30:     System.Threading.Thread.CurrentThread.CurrentCulture = systemCultureInfo;
  31: }
Share

Tags: , ,

  2. someguy198650’s avatar

    someguy198650 on March 12, 2010 at 4:15 AM

    What if I set the CurrentThread.CurrentCulture = “en-US” and settings still does not match the installed language of Office (maybe German or Japanese Excel), would it throw another exception?

    Reply

  3. Valentino Vranken’s avatar

    Valentino Vranken on March 13, 2010 at 3:00 PM

    According to the description in the KB article (the Cause chapter) I believe this issue only occurs when using the English version of Excel. I only have Office in English but if you’ve encountered it using a localized version, I suggest you to add a comment through the KB page.

    Regards,
    Valentino.

    Reply

  5. Stephane’s avatar

    Stephane on January 30, 2012 at 4:25 PM

    Excellent,
    This helped me to solve my problem with french version of Excel, when I use the Range’s property NumberFormat (or NumberFormatLocal).
    Each Time, NumBerFormat gives the same as NumberFormatLocal, the french Format (with a Date)
    If I write this before the new instance of Excel, it works as expected.
    System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo(“en-US”)
    Thank you very much indeed !

    Reply

    1. Valentino Vranken’s avatar

      Valentino Vranken on January 30, 2012 at 9:11 PM

      Glad to hear that blog posts can still be useful, even when they’re more than three years old! :)

      Reply

  6. Stephane’s avatar

    Stephane on January 30, 2012 at 9:24 PM

    Besides,
    The same lines had a different behavior in VB.Net (with Excel automation) and in VBA (Excel VBA).
    “MyCell.NumberFormat” in VBA returns english format
    “MyCell.NumberFormat” in VB.Net always returns french format. Changing the CurrentCulture was the only way I found (after 2 hours) to get the english format.
    Thanks again. Hard to find.

    Reply

© 2008-2012 A Developer's Blog All Rights Reserved