Using the number format field in excel buffer

ramsay18477

Hi All,

Iam exporting certian data including numeric ones to an excel sheet using the excel buffer table.

Although Iam doing the round(<number>,0.01), since the numeric data is 'format'ed, it cuts off all the trailing 0s after the decimal in the number. But, I want to export it as it is.
For eg, 3.60 is exported as 3.6 but I want it to be exported as 3.60.

For this Iam trying to use the 'number format' field in excel buffer table to format the numeric data as in excel, but I have not been able to find out the exact excel numeric format that I have to write in the code to pass to this field so it can export numeric data as per my requirement to excel.

Can anyone please help ?

Thanks in advance.

Xypher

The way I get around this issue is by setting the entire Excel document as Text. (And remove the Error Checking Options to notify the user a Number or Date is being displayed as text...)

That way anything sent to Excel shows AS IS.

Below sets the entire document to Text format.

ExcelApp.ActiveCell.EntireRow.EntireColumn.NumberFormat := '@';

Below sets the Error Checking Options.

ExcelApp.ErrorCheckingOptions.NumberAsText := FALSE;
ExcelApp.ErrorCheckingOptions.TextDate     := FALSE;

ramsay18477

Hi Xypher,

Thanks a lot for your reply.

The way I get around this issue is by setting the entire Excel document as Text

Iam indeed doing the normal export of data to excel by this method, the only difference being Iam using 'FORMAT' to pass the data as text to Excel Buffer through a function.

But, as I have written in my query, the FORMAT chops off the trailing 0.

Iam trying to use the NumberFormat field / property in the Excel Buffer table to format my numeric data, but am unable to find out the correct Excel format to pass to the table, whether it should be '999999.99' or '######.##' or anything else.

Thanks again.

Xypher

Take a look at this... (I prefer doing this over using the Excel Buffer, it's faster, and customizable.)

See if it can help you out at all.

Hanen

Hi,

What should I do if I want to get a decimal value like this format : xxxxxxx,xx "," and not "."