SAVEASEXCEL in old format

aceX

Hi there

I have client which is working on Dynamics NAV 2017. There is one function which is creating one of the reports in excel and send on their customer e-mail. The problem starts when some of the customers use old xls formats and they can open only xls formats. When I'm using SAVEASEXCEL function the format by default is xlsx ... I've tried to change name with DocumentNo.xls in extension but in background the file is xlsx - not xls.

So, my question is: Do we have in Dynamics NAV 2017 option for saving files in old excel format (xls).

Best Regards

AlexDen

Hello,

You can use Excel.Interop to open the file and save it in the required format.
Below is an example how to do this in Nav 2017, in other versions you have to change version of xlHelper variable, you can see your version in "Excel Buffer" table or just copy it from there.

In this example, file is opened on the client side. if you want to do this on the service side, then Excel must be installed on the server.

OBJECT Codeunit 50001 Save as xls
{
  OBJECT-PROPERTIES
  {
    Date=03.06.20;
    Time=09:00:00;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=VAR
            FileMgt@1000000000 : Codeunit 419;
            FileName@1000000001 : Text;
            NewFileName@1000000002 : Text;
          BEGIN
            FileName := FileMgt.OpenFileDialog('Select xlsx file','*.xlsx','');

            IF FileName = '' THEN
              EXIT;

            NewFileName := SaveAsXls(FileName);

            MESSAGE('File %1 has been exported to %2',FileName,NewFileName);
          END;

  }
  CODE
  {

    LOCAL PROCEDURE SaveAsXls@1000000001(FileName@1000000003 : Text) : Text;
    VAR
      FileMgt@1000000006 : Codeunit 419;
      XlApp@1000000002 : DotNet "'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass" RUNONCLIENT;
      XlWrkBk@1000000001 : DotNet "'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.Workbook" RUNONCLIENT;
      XlFileFormat@1000000004 : DotNet "'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.XlFileFormat" RUNONCLIENT;
      XlHelper@1000000000 : DotNet "'Microsoft.Dynamics.Nav.Integration.Office, Version=10.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.Integration.Office.Excel.ExcelHelper" RUNONCLIENT;
      PathHelper@1000000005 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.Path";
    BEGIN
      XlApp := XlApp.ApplicationClass;
      XlWrkBk := XlHelper.CallOpen(XlApp,FileName);

      FileName := PathHelper.ChangeExtension(FileName,'xls');
      IF FileMgt.ClientFileExists(FileName) THEN
        FileMgt.DeleteClientFile(FileName);

      XlHelper.CallSaveAsFormat(XlWrkBk,FileName,XlFileFormat.xlExcel8);

      CLEAR(XlWrkBk);

      XlHelper.CallQuit(XlApp);
      CLEAR(XlApp);

      EXIT(FileName);
    END;

    BEGIN
    END.
  }
}

AlexDen

Hello,

You can use Excel.Interop to open the file and save it in the required format.
But if you want to do it on the service side, then Excel must be installed there.

xStepa

Hi @AlexDen, I don't think so - it's not allowed on server side.

AlexDen

Hi @xStepa,

It looks like you are right, it's possible to use only on client side.

AlexDen

Hello,

You can use Excel.Interop to open the file and save it in the required format.
Below is an example how to do this in Nav 2017, in other versions you have to change version of xlHelper variable, you can see your version in "Excel Buffer" table or just copy it from there.

In this example, file is opened on the client side. if you want to do this on the service side, then Excel must be installed on the server.

OBJECT Codeunit 50001 Save as xls
{
  OBJECT-PROPERTIES
  {
    Date=03.06.20;
    Time=09:00:00;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=VAR
            FileMgt@1000000000 : Codeunit 419;
            FileName@1000000001 : Text;
            NewFileName@1000000002 : Text;
          BEGIN
            FileName := FileMgt.OpenFileDialog('Select xlsx file','*.xlsx','');

            IF FileName = '' THEN
              EXIT;

            NewFileName := SaveAsXls(FileName);

            MESSAGE('File %1 has been exported to %2',FileName,NewFileName);
          END;

  }
  CODE
  {

    LOCAL PROCEDURE SaveAsXls@1000000001(FileName@1000000003 : Text) : Text;
    VAR
      FileMgt@1000000006 : Codeunit 419;
      XlApp@1000000002 : DotNet "'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass" RUNONCLIENT;
      XlWrkBk@1000000001 : DotNet "'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.Workbook" RUNONCLIENT;
      XlFileFormat@1000000004 : DotNet "'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.XlFileFormat" RUNONCLIENT;
      XlHelper@1000000000 : DotNet "'Microsoft.Dynamics.Nav.Integration.Office, Version=10.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.Integration.Office.Excel.ExcelHelper" RUNONCLIENT;
      PathHelper@1000000005 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.Path";
    BEGIN
      XlApp := XlApp.ApplicationClass;
      XlWrkBk := XlHelper.CallOpen(XlApp,FileName);

      FileName := PathHelper.ChangeExtension(FileName,'xls');
      IF FileMgt.ClientFileExists(FileName) THEN
        FileMgt.DeleteClientFile(FileName);

      XlHelper.CallSaveAsFormat(XlWrkBk,FileName,XlFileFormat.xlExcel8);

      CLEAR(XlWrkBk);

      XlHelper.CallQuit(XlApp);
      CLEAR(XlApp);

      EXIT(FileName);
    END;

    BEGIN
    END.
  }
}

stefano77

Great!!!!!!!!!!!