send a formula to exceL and return the result

kanika
kanika Member Posts: 247
edited 2018-07-25 in NAV Three Tier
hello again experts!!

I need to send a formula to excel from a CU and return the result.
So far I have only used Excel to export data from reports but I have never needed you to return data and I do not know how to do it


this is my code by now;

txtFormula:='='+txtCadena;
ExcelBuf.AddColumn(txtFormula,TRUE,'',FALSE,FALSE,FALSE,'',0);
excelbuf.CreateBookAndOpenExcel('E:\Calcula.xlsx','Calculadora','Datos',COMPANYNAME,USERID);
ExcelBuf.GiveUserControl;

Best Answer

  • kanika
    kanika Member Posts: 247
    Answer ✓
    Hi experts!!
    finally it works perfectly with only 3 lines of code and without using excel buffer

    IF ISCLEAR(App) THEN CREATE(App,FALSE,TRUE);
    App.Visible := FALSE;
    decResultado:=App.Evaluate(txtFormula);

    ·App , Automation, 'Microsoft Excel 15.0 Object Library'.Application
    ·decResultado,decimal variable with the result
    ·txtFormula,variable text that contains the formula

    I hope it serves you, thanks everyone for your help

Answers

  • imclever1205
    imclever1205 Member Posts: 94
    Hi Kanika ,

    Did you try using the function WriteCellFormula in the Excel Buffer ?
  • kanika
    kanika Member Posts: 247
    edited 2018-07-26
    Hello imclever!!
    that function is not in excel buffer

    look:

    fxn1tmv9ya6t.png

    however, in table 370 I do see it, why can not I use it?

    j70bbgbu1mnj.png

    work with nav 2018
  • kanika
    kanika Member Posts: 247
    I have set the function as global (it was local)

    and now how do I use it?
    How do I send my formula and how do I get the result?
  • kanika
    kanika Member Posts: 247
    OK!!
    I have the solution here

    https://blogs.msdn.microsoft.com/nav/2012/10/04/excel-buffer-using-open-xml-instead-of-excel-automation-part-1-of-2/

    but I get this error

    3hf9rcmytwa6.png


  • kanika
    kanika Member Posts: 247
    I have administrator permissions and I am trying to create the excel in a shared folder for all the urinals with full control and I still can not create it

    a9zrsmxn1bi8.png
  • cvanderpoel
    cvanderpoel Member Posts: 9
    I'm not for sure, but does the path exists on the server (location servicetier)?
    Because the Microsoft.Dynamics.Nav.OpenXml dll is not running on client mode

    4sxwskaogsc3.png






  • kanika
    kanika Member Posts: 247
    of course, I'm working on the server and the tests I'm doing are all on the server
  • gcoelle
    gcoelle Member Posts: 8
    Please consider, that the Excel file will be written in the context of ApplicationServer process on the server and not by you directly. So please make sure, that the user under which the AppServer runs has the correct permissions to write to the directory.
    But this is just a dirty hack. In a productive environment, you should create the file in a temp directory on the server and then download it to any client directory with your permissions. See the methods in the Codeunit "File Managment" for further details.
  • kanika
    kanika Member Posts: 247
    Answer ✓
    Hi experts!!
    finally it works perfectly with only 3 lines of code and without using excel buffer

    IF ISCLEAR(App) THEN CREATE(App,FALSE,TRUE);
    App.Visible := FALSE;
    decResultado:=App.Evaluate(txtFormula);

    ·App , Automation, 'Microsoft Excel 15.0 Object Library'.Application
    ·decResultado,decimal variable with the result
    ·txtFormula,variable text that contains the formula

    I hope it serves you, thanks everyone for your help

Categories