Excel automation

You are currently browsing articles tagged Excel automation.

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: , ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved