Start Excel from RTC

24 Sep

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))’.

image

 

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:

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;

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

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;

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.

9 thoughts on “Start Excel from RTC

  1. Pingback: Export to Excel on RTC using .Net Interop « Microsoft Dynamics NAV Thoughts

  2. Pingback: Start Excel from RTC | Pardaan.com

  3. 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?

  4. 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!

  5. 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

  6. 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

Leave a Reply

Your email address will not be published.