Excel Range: Decimal Places

ta5

Hi
I want to export a value from Navision 3.7 to Excel 2000.

This works ok, but there is a problem with the number of decimal places.
Only 1.12 is shown in Excel.

xlWorkSheet2.Range('N1').NumberFormat := '#.0000';
xlWorkSheet2.Range('N1').Value := 1.1234;

When entered a value manually into this excel cell, it shows all decimal places.

I found this, but it did not help:
http://www.mibuso.com/forum/viewtopic.php?t=761


Many thanks
Thomas

kriki

I went into Excel, started recording a macro and changed to format of a cell, stopped recording and checked what Excel had written into the macro:

xlWorkSheet2.Range('N1').NumberFormat := '0.0000';

ta5

Hi Alain

Thx for your answer. Unfortuntaly it's still the same:
1.1200 is written to to cell (in my previous posting, I mentioned 1.12, this was wrong).

Thomas

ta5

The topic is partly solved

xlWorkSheet2.Range('N1').Value := Format(1.1234);
"Format" did the trick.

BTW:
I also checked with ...Value := SomeVariantValue, but this did not help.

cssgyula

Hi,

I try NumberFormat and NumberFormatLocal under NAV 5.0 SP1 hungarian and with Excel 2007, but I cannot set any of these parameters. I just don't know how to give them a formatting value, because the end of trying is a message for the C/AL programmer... :oops: ](*,)

Do you have experience with this?

Thanks in advance,

Savatage

cssgyula wrote:

I just don't know how to give them a formatting value, because the end of trying is a message for the C/AL programmer... :oops: ](*,)

Examining this one really helped me. See how Number Format has been added to the buffer
http://igor.beeone.googlepages.com/BorderExample.zip

If fact you can change the format with code very easily - see below

EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;Italic : Boolean;UnderLine : Boolean) : Text[250]
TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE("Row No.",RowNo);
TempExcelBuffer.VALIDATE("Column No.",ColumnNo);
TempExcelBuffer."Cell Value as Text" := CellValue;

IF (ColumnNo = 15)
 THEN TempExcelBuffer.NumberFormat := '#,##0.00'
 ELSE TempExcelBuffer.NumberFormat := '0';

TempExcelBuffer.Formula := '';
TempExcelBuffer.Bold := Bold;
TempExcelBuffer.Italic := Italic;
TempExcelBuffer.Underline := UnderLine;
TempExcelBuffer.INSERT;

example/ Now if I just put TempExcelBuffer.NumberFormat := '#,##0.00' then the whole report would be that format.
or you can change it depending on other values