NAV2016 - Usage of Excel Interop

t_cracco

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

t_cracco

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; }

kylehardin

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.

archer89

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/

t_cracco

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; }

mdPartnerNL

a side question if allowed: with xls interop the process is run on the server or client?

t_cracco

I'm using it client side

mdPartnerNL

ok, so is this still fast enough?

t_cracco

don't have any problems with it.

kylehardin

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.

mdPartnerNL

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

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

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?

t_cracco

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