Excel Automation with NAS

cjaney · 2017-11-17

I have a codeunit that updates an Excel Spreadsheet in order to provide inventory feed to customers. This works fine when I run from a client. Works fine when I run the codeunit on the server. Works fine when I run the codeunit in client on NAS Machine. But when I put the codeunit in the Job Queue it bombs trying to create the Excel Automation object. I have assured that the NAS Service User has Admin rights, but I can't get past this error!!! I have read threads and posts and google articles and tried so many things, but no avail.

It blows up as soon as it hits this code:

CREATE(MSExcel,FALSE,TRUE);

Variable Declaration is as follows.

Name Subtype DataType Length
MSExcel 'Microsoft Excel 16.0 Object Library'.Application Automation

I realize that the false, true is setting it to create on the client, but you can't save and compile if the second option is not TRUE, unless I am missing something simple.

It errors saying that Client Callbacks are not supported.

There are no dialogs being shown. Hasn't gotten to the point of actually doing anything, just trying to create the Object.

HELP!

Slawek_Guzek · 2017-11-17

You cannot use Automation if you want to run the code through Job Queue. You need to use DotNet.

Also I'm afraid that build-in excel files manipulation .NET are insufficient for what you want to do. The trouble is that when you write a cell the formatting is lost. If you can live with this then look into T370, or use functions available in it.

If you have to preserve formatting you may need to something else. You can use standard OpenXML objects or have a look at ClosedXML library, which is a derivate of OpenXML but much easier to use.

Slawek

cjaney · 2017-11-17

At this point being able to write a cell would be sufficient. I have tried using the DotNet Interop variables in place of my Automation variables, but can't seem to figure out the proper code to allow me to actually write anything. I DO NOT want to use the buffer because it is too slow and I am processing several thousand rows at a time. The whole intent is to move this to the job queue because the automation objects are too slow as well, and the buffer does not seem to perform much (if any) better.

ALSO, these files have to be in .xls NOT .xlsx format, and much of what I have found only seems to function with the latter.

I have the following code currently for dotnet, but it does not write anything or update the spreadsheet at all.

MsExcel := MsExcel.ApplicationClass;
IF ISNULL(MsExcel) THEN
ERROR('Excel Not Found');

MsExcel.DisplayAlerts:=FALSE;
MsExcel.Visible := TRUE;

IF FileExists THEN BEGIN
MSWorkbook := MsExcel.Workbooks.Open(ExcelPath2,
0,FALSE,5,'','',TRUE,EnumToInt(MsExcelPlatName, 'xlWindows'),'\t',TRUE,FALSE,0,
TRUE,1,0);

MSWorksheet := MSWorkbook.Worksheets.Item(1);
END;

MSRange := MSWorksheet.Range('A10','A10');
MESSAGE('%1',MSRange.Value);
//THE ABOVE RETURNS NULL EVEN WHEN A RANGE IS SPECIFIED THAT CONTAINS A VALUE

MSRange.Value := 'TEST';
//THE ABOVE DOES NOT WRITE ANYTHING TO THE SPREADSHEET

MsExcel.DisplayAlerts(FALSE);
MSWorkbook.Save;//

MsExcel.Quit;

Slawek_Guzek · 2017-11-17

Excel buffer used to be slow in pre-2103 versions and the reason of its slowness was using Excel to make spreadsheet reads/writes.

It is no longer the case in version 2013 onwards, because it does not use Excel anymore, it opens files directly and manipulates their structure through the OpenXML library, which is wrapped in NAV .NET interop object - which make things easier but also cut out lots of functionality.

But - OpenXML ony operates on .xlsx files. It has to be .xlsx I'm afraid - no mater if you use Excel Buffer or direct calls to NAV .NET wrapper, or OpenXML library objects.

Slawek

Excel Automation with NAS

Comments

Categories