HowTo: Fix “Old format or invalid type library” error (0x80028018)
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 0x80028018 (-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 If
2. 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 If
You 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
35 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:
https://office.microsoft.com/en-us/office-2003-resource-kit/office-and-windows-mui-packs-links-to-resources-HA001195321.aspx
https://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!
thx for a thousand…. its helping me out
Hi, is there any way to refer Office.MsoAppLanguageID.msoLanguageIDUI using late binding? Thank you.
Hi Gino,
You can use 2 because the msoLanguageIDUI constant of the MsoAppLanguageID enum has this value:
MsoAppLanguageID Enumeration
Thank you Dmitry for your answer.
My code has this:
m_objExcel = CreateObject(“Excel.Application”)
m_objOffice As Object = CreateObject(“Microsoft.Office.Core”)
newCulture = New System.Globalization.CultureInfo( _
CStr(m_objExcel.LanguageSettings.LanguageID(CInt(m_objOffice.MsoAppLanguageID.msoLanguageIDUI))))
The problem is here: CreateObject(“Microsoft.Office.Core”), I’ve tried “Office.dll”, “Office.Application”, and a lot of other combinations with no luck.
I always get “Cannot create ActiveX component” in this line.
Gino.
Gino,
Thank you for the code sample. Please try this one:
m_objExcel = CreateObject(“Excel.Application”)
newCulture = New System.Globalization.CultureInfo(CStr(m_objExcel.LanguageSettings.LanguageID(2)))
I agree every .NET programmer who develops Excel add-ins using C# or VB.NET must know about one specificity of the Excel Object Model. Most of the Excel Object Model methods and properties demand specifying an LCID.
Thank you for your feedback!
We also received this error when setting a cell value to a string that started with an equal sign but was not intended to be interpreted as a formula. (It was a generated strong password.) We solved the problem by starting the string with an apostrophe, which instructs Excel to interpret the value as a string.
excel.Cells[42,24] = “=fJ7)^3eh@”; // throws
excel.Cells[42,24] = “‘=fJ7)^3eh@”; // works with expected result
I have just tested the value you provided and got another exception – 0x800A03EC. Nevertheless, thank you for your comment!
Ah, yes that’s right. I looked at my search history and it looks like 0x800A03EC led me to a blog post at https://mcoxeter.wordpress.com/tag/0x800a03ec/, which in turn led me to yours. In Mike’s post 0x800A03EC was thrown on a set, and 0x80028018 on a get, and so they were listed together. My bad. Thanks for your reply.
Aha, that’s it. Thank you for clarification!
None of these solutions work for validation add when using a custom function. Nor does “just” setting it. I have not been able to get around the 0x800A03EC hresult when the language is a different locale UNLESS I set the formula names to be the translated formula names.
range.Validation.Add(Excel.XlDVType.xlValidateCustom, Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, “=ISNUMBER(VALUE(A1))”, Type.Missing);
Throws this exception regardless when running as German. If I change it to:
range.Validation.Add(Excel.XlDVType.xlValidateCustom, Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, “=ISTZAHL(WERT(A1))”, Type.Missing);
However I do not want to maintain a translation of the function names in my own resources. I would like to be able to either pull the translations from excel itself or to just use English like with all other formulas.
Any ideas?
Also, we do have an add-in express license, but we are just using a straight VSTO project for this particular add in. Just in case that matters.
Hi Tim,
I have no idea why Excel requires localized formula names in this method (probably this is one more bug in the Excel Object Model) but I have an idea of how to bypass this. Please have a look at the following code:
string formulaLocal = string.Empty;
Excel.Range dummyRange = (Globals.ThisAddIn.Application.ActiveSheet as Excel._Worksheet).Cells[100, 100];
dummyRange.Formula = “=ISNUMBER(VALUE(A1))”;
formulaLocal = dummyRange.FormulaLocal;
dummyRange.Clear();
Globals.ThisAddIn.Application.ActiveCell.Validation.Add(Excel.XlDVType.xlValidateCustom, Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, formulaLocal, Type.Missing);
Hope this will help you.
Holy moly Dmitry, that worked! I had already tried this exact method, but I was using .Formula, and then .FormulaR1C1 to pull it back out, but it was still untranslated.
Sorry to bother you again Dmitry, but it sometimes does not translate. Most of the times it does. Very confusing, any ideas why?
Figured out why it is not translating. Every once in a while when editing the formula it changes it from :
=ISNUMBER(VALUE(C11))
to:
=ISNUMBER(VALUE(R11C3))
Because the address is invalid it does not translate. But why would it convert it to that?
Hi Tim,
I can assume that it does not work when the R1C1 reference style is used in an Excel workbook. In this case you can try to use the FormulaR1C1Local property. Or convert the A1 style reference to the R1C1 style using the ConvertFormula method:
if (Globals.ThisAddIn.Application.ReferenceStyle == Excel.XlReferenceStyle.xlA1)
formulaLocal = dummyRange.FormulaLocal;
else
{
//formulaLocal = dummyRange.FormulaR1C1Local;
formulaLocal = Globals.ThisAddIn.Application.ConvertFormula(dummyRange.FormulaLocal, Excel.XlReferenceStyle.xlA1, Excel.XlReferenceStyle.xlR1C1, false, Globals.ThisAddIn.Application.ActiveCell);
}
Dmitry, thought I would let you know I figured out what the issue is. It was not what I thought previously. Turns out that when calculation is set to manual like so _excel.Calculation = Excel.XlCalculation.xlCalculationManual; then the .FormulaLocal does not translate unless a clear is issued upon the cell first. Not sure why. I am setting calculations to manual during a very heavy load process. This is what I ended up with:
public static string TranslateFormula(Excel.Worksheet ws, string formula, bool toAbsoluteAddresses = false)
{
string ret = String.Empty;
Excel.Range cell = ws.Range[“A1”].End[Excel.XlDirection.xlToRight];
cell.Clear(); //issue a clear because when calcs are set to manual, if the cell has not been first cleared the formulalocals will not work.
try
{
if (ws.Application.ReferenceStyle == Excel.XlReferenceStyle.xlA1)
{
cell.Formula = ws.Application.ConvertFormula(formula, Excel.XlReferenceStyle.xlA1, Excel.XlReferenceStyle.xlA1, toAbsoluteAddresses, Type.Missing);
ret = cell.FormulaLocal;
}
else
{
cell.Formula = ws.Application.ConvertFormula(formula, Excel.XlReferenceStyle.xlA1, Excel.XlReferenceStyle.xlR1C1, toAbsoluteAddresses, Type.Missing);
ret = cell.FormulaR1C1Local;
}
}
finally
{
if (cell != null)
{
cell.Clear();
cell.Release();
}
}
return ret;
}
Tim,
Thank you for posting your code and keeping us informed.
I am not getting the “old format” exception, I am getting “0x800A03EC at System.RuntimeType.ForwardCallToInvokeMember” which the guy at this link (https://mcoxeter.wordpress.com/2011/03/25/excel-com-issue-solved/) seems to say he fixed using your “Change the System.Threading.Thread.CurrentThread.CultureInfo” method.
But I am using a DLL for Adobe Illustrator, not Excel. Do you think it is likely that the exception is being thrown for the same reasons and that I can fix it using the “Change the System.Threading.Thread.CurrentThread.CultureInfo” method? If not, then what exactly is the ForwardCallToInvokeMember routine for and what else could cause this exception to be thrown?
Hi Skip,
I am sorry, but I don’t know how to solve the 0x800A03EC error in an Adobe Illustrator DLL. Maybe it makes sense to contact Adobe support guys for assistance.
hi dmitry
i have a case where i would like convert from .xls to .xlsb and this works fine but when i change to german i get the 0x80028018 error. where/ how do i apply this solution, where do i pu the cultureInfo objects. Here’s my code:
Dim excelApplication As Excel.Application = New Excel.Application()
Dim workbook As Excel.Workbook = excelApplication.Workbooks.Open(strTempPath, Excel.XlUpdateLinks.xlUpdateLinksNever, True, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
FilePath = Microsoft.VisualBasic.Strings.Replace(FilePath, “.xls”, “.xlsb”)
workbook.SaveAs(FilePath, Excel.XlFileFormat.xlExcel12, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
workbook.Close(False, Type.Missing, Type.Missing)
excelApplication.Quit()
I appreciate your assistance
Hi Pizzara,
I think you can use the InvokeMember method. Please have a look at the code samples in this post. #1 sample uses methods and properties of the Excel Object Model directly, #2 sample uses the same methods and properties but via InvokeMember.
Hola me sale el sgtes error:
System.Runtime.InteropServices.COMException (0x80028018): Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
Private Sub GrabarExcelcomoCSV(ByVal pstrRutaOrigen As String, ByVal pstrArchivoConExtension As String, ByVal pstrRutaDestino As String, ByVal pstrArchivoSinExtension As String, ByVal pstrExtension As String)
Try
Dim inIndiceHoja As Integer = 1
Dim objXlApp As Object = CreateObject(“Excel.Application”)
Dim objXlBook As Object
Dim objXlSheets As Object
Dim strArchivoSinExtension As String
‘Cargar el Excel Workbook para grabar
objXlBook = objXlApp.Workbooks.open(pstrRutaOrigen & pstrArchivoConExtension, Password:=PRO_strContraseña)
‘Aplica el formato antes de grabar a CSV
objXlApp.DecimalSeparator = DPLA_strSimboloDecimal
objXlApp.ThousandsSeparator = ” ”
objXlApp.UseSystemSeparators = False
For inPosSheet As Integer = 1 To objXlBook.Worksheets.Count
Dim strRutaNombreArchivo As String = “”
objXlSheets = objXlBook.Sheets(inPosSheet)
‘ selecciona la hoja actual con el método Select
objXlSheets.Select()
objXlSheets.Copy()
objXlApp.DisplayAlerts = False
strRutaNombreArchivo = pstrRutaDestino & pstrArchivoSinExtension & “_H” & CStr(inIndiceHoja) & pstrExtension
pstrArchivoConExtension = pstrArchivoSinExtension & “_H” & CStr(inIndiceHoja) & pstrExtension
strArchivoSinExtension = pstrArchivoSinExtension & “_H” & CStr(inIndiceHoja)
If File.Exists(strRutaNombreArchivo) Then
File.Delete(strRutaNombreArchivo)
End If
objXlApp.ActiveWorkbook.SaveAs(strRutaNombreArchivo, 6) ‘Excel.XlFileFormat.xlCSV
objXlApp.ActiveWorkbook.Close(False)
inIndiceHoja += 1
Next
objXlBook.Close() ‘True= Graba cambios
ForzarCierreEXC(objXlBook)
objXlApp.Quit() ‘Cierra Excel
ForzarCierreEXC(objXlApp)
Thread.CurrentThread.CurrentCulture = OldCulture
Catch ex As Exception
Util.Log.GrabaRegistroLog(gstrRutaLog, cnsNombreModulo, ex.ToString)
Throw ex
Finally
GC.Collect()
End Try
End Sub
Hi Richard,
To fix the error, you need to change the System.Threading.Thread.CurrentThread.CultureInfo property before calling Excel methods.
This is what I used in order to fix the CurrentCulture error for my sript to control excel worksheets in PowerShell 4.0:
Function Using-Culture (
[System.Globalization.CultureInfo]$culture,
[ScriptBlock]$script)
{
$OldCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture
trap
{
[System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
}
[System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
$ExecutionContext.InvokeCommand.InvokeScript($script)
[System.Threading.Thread]::CurrentThread.CurrentCulture = $OldCulture
} # End Function
Using-Culture en-us{
#whatever you want to execute that is throwing back an “Old format or invalid type library” error.
#it could be the whole script
}