How to Merge Excel Cells in NAV 2016 with DotNet variables.

borjags1985

I want to merge Excel cells with Dotnet variables from the Excel Buffer table but I can not find clear information about that.

I could change Font color and size and define cell borders too, but I have not been able to merge cells.


Has anyone seen something about that?


Thanks!!

RockWithNAV

Hey,

Check out this link it may help you.

[url="http://forum.mibuso.com/discussion/15266/merging-cells-in-excel-using-excel-
buffer"]forum.mibuso.com/discussion/15266/merging-cells-in-excel-using-excel-buffer[/url]

This piece of code

XlBook := XlApp.Workbooks.Open(FileName);

//Transfering Data to Microsoft Excel:
XlSheet:= XlApp.ActiveSheet;
XlSheet.Range('A1:A2').Cells.Merge;
XlSheet.Range('A1').HorizontalAlignment := -4108;

borjags1985

I'm trying to set that code but it doesn't work.

On table 370, before calling GiveUserControl function and after calling OpenExcel, I call a new function called fMergeCells. This function use the vars in the table, so they must be initialized.

That is my function:

fMergeCells(pInitRange : Text;pEndRange : Text)
IF (pInitRange = '') OR (pEndRange = '') THEN
ERROR(ErrorText50000);

XlWrkSht := XlApp.ActiveSheet;
XlWrkSht.Range(pInitRange + ':' + pEndRange).Cells.Merge('');

And this is the error:

"A Dotnet variable has not been instantiated. Attempting to call Cells in Table Excel Buffer: fMergeCells"

borjags1985

Finally I have been able to solve the problem. In addition, i have put the code in the 370 table to standarize it. So, firstly the sheets are writen with TempExcelBuffer.WriteSheet. After that I call a function to apply the format to the Range (in this function you can insert any code that can be managed with the Range DotNet var).

What I have done in that function is to create another var for Range Method: XlRange DotNet Microsoft.Office.Interop.Excel.Range.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' .

This var has to be instanziated like this:
XlWrkSht := XlWrkBk.Worksheets.Item(pWrkShtName);

After that I instanziate the Range var:
XlRange := XlWrkSht.Range('A1:B1');

Finally I use any function in that var:
XlRange.Cells.Merge(TRUE);
XlRange.HorizontalAlignment := 1;
XlRange.VerticalAlignment := 1;

borjags1985

You have to set the RunOnClient property of Range DotNet var to YES.

Duikmeester

This was assumed as being already done

chachitano@hotmail.com

Hi, should this work in NAV 2015?
I am getting on run time:

******
A DotNet variable has not been instantiated. Attempting to call Microsoft.Office.Interop.Excel.Range.Cells in Table Excel Buffer: MergeRange
******

Vitomir10

Same error here as @chachitano@hotmail.com . Can someone tell us how to solve the problem ? I have tried many ways to instantiate it, but without a positive result.

Vitomir10

@chachitano@hotmail.com I solve it .. just make the function , for the merge , to be called from PostOpenExcel() function.