Problem
Starting the Excel application from the Role Tailored Client can result in an error if the Regional Settings do not match the installed language of Office. In my case, I have Dutch Regional Settings but Office is installed with English (United States). When calling the Workbooks.Add method, I receive the error message ‘The call to member Add failed: Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))’.
The cause of this error is well described in this Knowledge Base article from Microsoft: http://support.microsoft.com/kb/320369. The article also provides some workarounds. In this post the first workaround is implement in C/AL code.
Solution
In my previous post about creating Excel files via .Net Interop, I showed an example of function GiveUserControl in Table 370:
[codesyntax lang=”progress”]
PROCEDURE GiveUserControlDotNet@150002040();
VAR
MemoryStream@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
ExcelApp@150002031 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel._Application" RUNONCLIENT;
ExcelAppClass@150002030 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass" RUNONCLIENT;
NVInStream@150002025 : InStream;
ThreeTierMgt@150002027 : Codeunit 419;
FileNameRTC@150002028 : Text[1024];
BEGIN
// .Net >>
MemoryStream := MemoryStream.MemoryStream();
XlWrkBkDotNet.SaveAs(MemoryStream);
NVInStream := MemoryStream;
DOWNLOADFROMSTREAM(NVInStream,'',ThreeTierMgt.Magicpath,'',FileNameRTC);
ExcelApp := ExcelAppClass.ApplicationClass();
ExcelApp.Workbooks.Add(FileNameRTC);
ExcelApp.Visible(TRUE);
ExcelApp.UserControl(TRUE);
// .Net <<
END;[/codesyntax]
The workaround describes how to change the CultureInfo that is used when calling the Add or Open function of an Excel Workbook. The CultureInfo can be changed using .Net Reflection. The next code example implements the workaround. Click here to open the code in a new window: GiveUserControlDotNet with Culture Info
[codesyntax lang=”progress”]
PROCEDURE GiveUserControlDotNet@150002040();
VAR
MemoryStream@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
ExcelApp@150002031 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel._Application" RUNONCLIENT;
ExcelAppClass@150002030 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass" RUNONCLIENT;
Workbook@150002037 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel._Workbook" RUNONCLIENT;
Workbooks@150002036 : DotNet "'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.Workbooks" RUNONCLIENT;
MsoAppLanguageID@150002035 : DotNet "'office, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Core.MsoAppLanguageID" RUNONCLIENT;
ReflectionBindingFlags@150002034 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Reflection.BindingFlags" RUNONCLIENT;
CultureInfo@150002033 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Globalization.CultureInfo" RUNONCLIENT;
Type@150002032 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Type" RUNONCLIENT;
RuntimeTypeHandle@150002029 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.RuntimeTypeHandle" RUNONCLIENT;
Args@150002038 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array" RUNONCLIENT;
DummyArray@150002026 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array" RUNONCLIENT;
NVInStream@150002025 : InStream;
ThreeTierMgt@150002027 : Codeunit 419;
FileNameRTC@150002028 : Text[1024];
CultureID@150002039 : Integer;
BEGIN
// .Net >>
MemoryStream := MemoryStream.MemoryStream();
XlWrkBkDotNet.SaveAs(MemoryStream);
NVInStream := MemoryStream;
DOWNLOADFROMSTREAM(NVInStream,'',ThreeTierMgt.Magicpath,'',FileNameRTC);
ExcelApp := ExcelAppClass.ApplicationClass();
Workbooks := ExcelApp.Workbooks;
RuntimeTypeHandle := Type.GetTypeHandle(FileNameRTC);
Type := Type.GetTypeFromHandle(RuntimeTypeHandle);
Args := Args.CreateInstance(Type,1);
Args.SetValue(FileNameRTC,0);
ReflectionBindingFlags := 0;
ReflectionBindingFlags := ReflectionBindingFlags.Parse(ReflectionBindingFlags.GetType(), 'InvokeMethod');
MsoAppLanguageID := 0;
MsoAppLanguageID := MsoAppLanguageID.Parse(MsoAppLanguageID.GetType(), 'msoLanguageIDUI');
CultureID := ExcelApp.LanguageSettings.LanguageID(MsoAppLanguageID);
CultureInfo := CultureInfo.CultureInfo(CultureID);
RuntimeTypeHandle := Type.GetTypeHandle(Workbooks);
Type := Type.GetTypeFromHandle(RuntimeTypeHandle);
Workbook := Type.InvokeMember('Add', ReflectionBindingFlags, DummyArray, Workbooks,Args, CultureInfo);
ExcelApp.Visible(TRUE);
ExcelApp.UserControl(TRUE);
// .Net <<
END;[/codesyntax]
The only difference with the workaround from the Knowledge Base article is, that it is not using hardcoded “en-US”, but instead uses the actually installed UI language of Office.
This examples calls the method ‘Add’ on ExcelApp.Workbooks. That forces Excel to create a new workbook based on a template. You can replace ‘Add’ with ‘Open’ to open an existing Excel file.
Pingback: Export to Excel on RTC using .Net Interop « Microsoft Dynamics NAV Thoughts
Pingback: Start Excel from RTC | Pardaan.com
Hello,
I implemented this approach in order to get past the error with method Add. But now I get the following error when compiling on the line “Workbook := TYPE.InvokeMember(‘Add’, ReflectionBindingFlags, DummyArray, Workbooks,Args, CultureInfo);”:
—————————
Ambigous function call, no matching method found.
—————————
I read on another post of yours that I must not use Null parameter. But there is no null parameter here. Do you have any other hint?
Regards,
Claude
Problem solved. My guess is that I use a newer version of the API and the parameters have changed.
But now I get the following error:
“The call to member InvokeMember failed: Add method of Workbooks class failed.”
Don’t know where else to look.
What exactly did you do to resolve the
“Workbook := TYPE.InvokeMember(‘Add’, ReflectionBindingFlags, DummyArray, Workbooks,Args, CultureInfo);”:
—————————
Ambigous function call, no matching method found.
I am using the code exactly as listed above and I am getting this error.
I see that the DummyArray is not populated anywhere in the code. and when I try I get “Assignment is not allowed for this Variable”
I really need to get this to work. Did you ever resolve this? What pieces of code did you comment out?
It seems that it’s related to problems from my excel buffer. For example, I call the merge method with parameters (RowEnd, ColEnd, RowStart,ColStart) instead of (RowStart,ColStart, RowEnd, ColEnd). The error message tells something else and the debugger shows me the GiveUserControl function … useless. I had to comment pieces of my code to find the problem and now it works just fine.
Regards!
I am just trying to compile the Table 370 not run it when I get the “The Function call was ambiguous. No Matching method was found.”
DummayArray was Null in the original code above so I added
DummyArray := Args;
The error message The Function call was ambiguous. No Matching method was found.”
Stops on the
Workbook := TYPE.InvokeMember(‘Add’, ReflectionBindingFlags, DummyArray, Workbooks,Args, CultureInfo);
Can someone please help?
Hi Cheryl,
If you add a variable ‘ReflectionBinder’ of type System.Reflection.Binder.’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
Then you can add this a parameter into the call, like this:
XlWrkBk := TYPE.InvokeMember(‘Add’, ReflectionBindingFlags, ReflectionBinder, XlWrkBks, Args, CultureInfo);
So remove the DummyArray
The easiest way is first find your system non-unicode setup from Control Panel. Then move to NAV languages to search that code and get the windows language ID. Move to your excel installation folder, copy EXCEL.EXE and then create a new folder named as that windows language ID and put the copied EXCEL.EXE but renamed as xllex.dll