NAV2016 - Usage of Excel Interop

t_cracco
t_cracco Member Posts: 32
in NAV Three Tier
Hi,

I'm exporting data and create charts to a new Excel file using the Excel Interop (.NET). But when I create a new workbook, only 1 sheet is created.
But my charts need to be added to separate sheets, for that I'm using the Workbook.Sheets.Add(before,after,number,type) - function.
Now I'm getting the error "Method Add of class Sheets failed".

the variables used (all marked with 'run on client'):
  • XlApplication: Microsoft.Office.Interop.Excel.ApplicationClass.'Microsoft.Office.Interop.Excel'
  • XlWorkBook: Microsoft.Office.Interop.Excel._Workbook.'Microsoft.Office.Interop.Excel'
  • XlWorkSheet_: Microsoft.Office.Interop.Excel._Worksheet.'Microsoft.Office.Interop.Excel'
  • missing:System.Reflection.Missing.'mscorlib'

My code looks the following: 
XlApplication:=XlApplication.ApplicationClass;
IF ISNULL(XlApplication) THEN
  ERROR(Text000);
XlApplication.Visible(FALSE);
XlWorkBook:= XlApplication.Workbooks.Add(XLWBATemplate.xlWBATWorksheet);
XlWorkSheet_ := XlWorkBook.ActiveSheet;
XlWorkSheet_.Name:='Demo1';
XlWorkSheet:=XlWorkBook.Sheets.Add(missing,XlWorkBook.Sheets.Item(XlWorkBook.Sheets.Count),missing,missing);
XlWorkSheet_.Name:='Demo2';
XlWorkBook.SaveCopyAs(FileName)

The error occurs on line:
XlWorkSheet:=XlWorkBook.Sheets.Add(missing,XlWorkBook.Sheets.Item(XlWorkBook.Sheets.Count),missing,missing);
because in the Add-function all 4 parameters are marked as optional, but in C/AL those parameters need to be added. Due to testing I figured out that the error is thrown because of the optional parameters, but I'm actually currently stuck in continuing my work.

I also tried to indicate to Excel that it needs to add 3 sheets when creating a new workbook using the function "SheetsInNewWorkbook", but that didn't change anything in the Excel file, only 1 sheet was created.

Is there anyone who knows how to deal with this problem?

Kind Regards,
Tom

Best Answer

Answers

  • kylehardin
    kylehardin Member Posts: 257
    I've seen the same thing when working with Word. If you make a bunch of dummy parameters for the tons of booleans and strings (text), you can put those in place as the parameters. But they are required.
    Kyle Hardin - ArcherPoint
  • archer89
    archer89 Member Posts: 337
    i have also tried to call xls-interop functions, which need the missing parameter. all tries failed. i tried the same as you, also system.object and others. nothing worked. so i decided to develop my own .net assembly, which references the excel interop assembly, that covers the needed functions. in the end it was then much easier to handle and costed less time. for my solution follow https://moxie4nav.wordpress.com/2016/08/26/simple-readwrite-excel-data/
    best regards
    Franz Kalchmair, MVP
    Alias: Jonathan Archer

    please like / agree / verify my answer, if it was helpful for you. thx.
    Blog: http://moxie4nav.wordpress.com/
  • mdPartnerNL
    mdPartnerNL Member Posts: 802
    a side question if allowed: with xls interop the process is run on the server or client?

  • t_cracco
    t_cracco Member Posts: 32
    I'm using it client side
  • mdPartnerNL
    mdPartnerNL Member Posts: 802
    ok, so is this still fast enough?
  • t_cracco
    t_cracco Member Posts: 32
    don't have any problems with it.
  • kylehardin
    kylehardin Member Posts: 257
    You will want those to run on the client. Office apps need a full profile, and usually NST services are run under something like NETWORK SERVICE or some other non-interactive account.
    Kyle Hardin - ArcherPoint
  • mdPartnerNL
    mdPartnerNL Member Posts: 802
    t_cracco wrote: »
    Hi Archer89, KyleHardin,

    I have experimented with some code yesterday and I finally got it working.
    I have created a DLL where I have only 1 function with returns System.Reflection.Missing.Value and use this in NAV to cover the optional parameters.
    public System.Reflection.Missing GetMissing()
{ return System.Reflection.Missing.Value; }

    Good for you. Could you share more of how you use this?
  • t_cracco
    t_cracco Member Posts: 32
    edited 2016-09-08
    mdPartnerNL wrote: »
    t_cracco wrote: »
    Hi Archer89, KyleHardin,

    I have experimented with some code yesterday and I finally got it working.
    I have created a DLL where I have only 1 function with returns System.Reflection.Missing.Value and use this in NAV to cover the optional parameters.
    public System.Reflection.Missing GetMissing()
{ return System.Reflection.Missing.Value; }

    Good for you. Could you share more of how you use this?

    Hi,

    sorry for my late response. Here you go.

    Variable:
    'xlMissing' (DotNet) : xlMissing.xlMissing.'xlMissing, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'

    init of the variable:
    XlMissing:=XlMissing.xlMissing;

    using it with Sheets.Add
    XlWorkSheet:=XlWorkBook.Sheets.Add(XlMissing.GetMissing,XlWorkBook.ActiveSheet,1,XLSheetType.xlWorksheet);
  • AntidotE
    AntidotE Member Posts: 61
    Hello @t_cracco I tried to do the same and managed to create dll.
    C# code: 
    using System;
namespace XlMissing
{
    public class XlMissing
    {
        public System.Reflection.Missing GetMissing()
        {
            return System.Reflection.Missing.Value;
        }
    }
}
    NAV code: 
    XLMissing := XLMissing.XlMissing;
XlWrkSht := XlWrkBk.Worksheets.Add(XLMissing.GetMissing,XLMissing.GetMissing,1,-4167);
    As a result get error:
    Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
    Any other suggestion?
    It is hard to swim against self bloodstream... (c) Old, experienced kamikadze.
  • t_cracco
    t_cracco Member Posts: 32
    AntidotE wrote: »
    Hello @t_cracco I tried to do the same and managed to create dll.
    C# code: 
    using System;
namespace XlMissing
{
    public class XlMissing
    {
        public System.Reflection.Missing GetMissing()
        {
            return System.Reflection.Missing.Value;
        }
    }
}
    NAV code: 
    XLMissing := XLMissing.XlMissing;
XlWrkSht := XlWrkBk.Worksheets.Add(XLMissing.GetMissing,XLMissing.GetMissing,1,-4167);
    As a result get error:
    Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
    Any other suggestion?

    Is your .net variable clientside defined? Cause I have the same problem when I run it on serverside with a NAS and I haven't got it solved yet.

    Tom

Categories