I think every .NET programmer who develops Excel add-ins using C# or VB.NET (with or without Add-in Express) should know about one specificity of the Excel Object Model. Most of the Excel Object Model methods and properties require specifying an LCID (locale identifier). If a client computer has the English version of Excel, and the locale for the current user is configured for another language (e.g. German or French), Excel may fire the “Old format or invalid type library” exception with error code 0×80028018 (-2147647512). The error usually occurs when the COM add-in code attempts to execute a method or get / set some property.
You can cope with this Excel error in three possible ways. The first one, and the simplest, is to install the Multilingual User Interface Pack to the client PC. The other 2 concern developers because they require coding, and we will dwell a bit more on them:
1. Change the System.Threading.Thread.CurrentThread.CultureInfo property before calling Excel methods or accessing properties. For example:
Dim excelWorkBook As Excel.Workbook
Dim excelSheets As Excel.Sheets
Dim Res As Boolean
Dim newCulture As System.Globalization.CultureInfo
Dim OldCulture As System.Globalization.CultureInfo
OldCulture = System.Threading.Thread.CurrentThread.CurrentCulture
newCulture = New System.Globalization.CultureInfo( _
ExcelApp.LanguageSettings.LanguageID(Office.MsoAppLanguageID.msoLanguageIDUI))
System.Threading.Thread.CurrentThread.CurrentCulture = newCulture
excelWorkBook = ExcelApp.ActiveWorkbook
excelSheets = excelWorkBook.Sheets
Try
' verify the Saved property
Res = excelWorkBook.Saved
If Res Then
System.Windows.Forms.MessageBox.Show("The Workbook is saved.")
Else
System.Windows.Forms.MessageBox.Show("The Workbook is not saved.")
End If
' add a worksheet
excelSheets.Add()
Catch ex As Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
System.Threading.Thread.CurrentThread.CurrentCulture = OldCulture
If excelWorkBook IsNot Nothing Then
Marshal.ReleaseComObject(excelWorkBook)
End If
If excelSheets IsNot Nothing Then
Marshal.ReleaseComObject(excelSheets)
End If2. Use the InvokeMember method. In this case you can directly specify CultureInfo for any call. For example:
Dim excelWorkBook, excelSheets As Object
Dim Res As Boolean
Dim newCulture As New System.Globalization.CultureInfo( _
ExcelApp.LanguageSettings.LanguageID(Office.MsoAppLanguageID.msoLanguageIDUI))
excelWorkBook = ExcelApp.ActiveWorkbook
excelSheets = Nothing
Try
' verify the Saved property
Res = excelWorkBook.GetType().InvokeMember( _
"Saved", Reflection.BindingFlags.GetProperty,_
Nothing, excelWorkBook, Nothing, newCulture)
If Res Then
System.Windows.Forms.MessageBox.Show("The Workbook is saved.")
Else
System.Windows.Forms.MessageBox.Show("The Workbook is not saved.")
End If
' add a worksheet
excelSheets = excelWorkBook.GetType().InvokeMember( _
"Sheets", Reflection.BindingFlags.GetProperty, Nothing,_
excelWorkBook, Nothing, newCulture)
excelSheets.GetType().InvokeMember( _
"Add", Reflection.BindingFlags.InvokeMethod, Nothing,_
excelSheets, Nothing, newCulture)
Catch ex As Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
If excelSheets IsNot Nothing Then
Marshal.ReleaseComObject(excelSheets)
End If
If excelWorkBook IsNot Nothing Then
Marshal.ReleaseComObject(excelWorkBook)
End IfYou can download VB.NET and C# samples that illustrate the code-based solutions for the “Old format or invalid type library” exception in Excel 2000 – 2007 using the links below.
You may also be interested in:
Building Excel COM add-in step-by-step
Creating Excel Automation add-ins
Developing Excel XLL add-ins
Sample Excel add-in with source code: VB.NET, C#, Delphi
Available downloads:
The sample add-ins below were written using Add-in Express for Office and .net
C# sample Excel add-in for VS 2005
VB.NET sample Excel add-in for VS 2005
9 Comments
Thanks for the info.
Thank you, this post helped me.
in your first option, you are using “Office.MsoAppLanguageID.msoLanguageIDUI”
where can i find the office type?
You can find the MsoAppLanguageID enum in the Office assembly (office.dll). You need to add this assembly to your project References.
Can you provide a link to the “Multilingual User Interface Pack”? Is that an Office install or a Windows install?
This is the Multilingual User Interface Pack for your version of Office. I have found a couple of links, please see below:
http://office.microsoft.com/en-us/office-2003-resource-kit/office-and-windows-mui-packs-links-to-resources-HA001195321.aspx
http://office.microsoft.com/en-us/downloads/office-language-interface-pack-lip-downloads-HA001113350.aspx
Thanks for the post, there are more posts about this subject but this one is very clear.
Thank you very very much.
Thanks very much. Saved my day!