Need help formatting number to output to Excel.

Business Central/NAV User Forum
1

I am using Navision 5.0 SP1 and writing a report to output g/l entries to excel.

All works well except the amounts are coming out raw. i.e. 1450 for 1,450.00.

I would like to output the amount to a fixed 2 position decimal to Excel.

gsTextAmount is a text field 30.

I am using a simple FORMAT command:

gsTextAmount := FORMAT(“G/L Entry”.Amount);

I have tried several options, i.e. gsTextAmount := FORMAT(“G/L Entry”.Amount,30,2); etc. but nothing helps.

Thanks in advance,

Greg

2

In my experience the formating in Excel needs to be done in excel. It was a long time ago that i did this but i beleive you use some Excelbuffer dll or equivalent and in that automation pack there should be functions to call to set format of cells in excel. Or even a full column.

/Jinnis

3

It’s Excel, which reformats your input - so it makes no difference how you format the numbers on the “Navision side”.

Try to type directly into Excel a number with 2 zeroes after dec.delimiter - as soon as you hit Enter or move away from that cell otherwise, the zeroes and dec.delimiter will disappear, unless you apply such format for cell BEFORE entering some number.

By default all cells initially has General formatting, and Excel tries to guess what was entered, sometimes it even changes to Date a number which looks like date and might be a date.

The only way is formatting the column / range through automation, but again - BEFORE actually filling the cells.

updated: Martin managed to answer first, while I was slowly typing [:)]

4

Thanks for the replies. I will have to try another idea…

5

Hi again Lovejazz

I looked around out of curiosity and found a standard object handling Export to Excel and where they use functions in the automation object for the Excel Sheet to set formating of columns and rows.

See table 370 Excel buffer and look into the function called CreateSheet. In this function a range of excel cells are formated in the Excel worksheet to be numbers. If you backtrack how it is done you might be able to something similar to that to solve your issue.

Best of luck

/Jinnis