send a formula to exceL and return the result

kanika

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;

kanika

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

imclever1205

Hi Kanika ,

Did you try using the function WriteCellFormula in the Excel Buffer ?

kanika

Hello imclever!!
that function is not in excel buffer

look:



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



work with nav 2018

kanika

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

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

kanika

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

cvanderpoel

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

kanika

of course, I'm working on the server and the tests I'm doing are all on the server

gcoelle

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

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