Excel buff Want to export numbers as numbers in Excel

SPost29

I want to have the numbers I export to Excel using the Excel buffer table to be numbers
when I open Excel.
I currently get "The number in this cell is formatted as text or preceded by an apostrophe"
Then there is a process I can choose: 'Convert to Number'
I want it to convert to number automatically so my users or I don't have to mess with it manually.

How can I get my exported numbers to be numbers?

Thanks
Steve

DaveT

Hi Steve,

How are using the Excel Buffer table?

Set the NumberFormat or set the last agrument on the AddColumn function as per formating a cell in Excel

SPost29

Hi Dave,
Thanks for your response

I am using on the OnPreSection of a report

EnterCell(RowNo,10,FORMAT("Outstanding Amount"),FALSE,FALSE,'@'); // for numbers

EnterCell(RowNo,3,"Vendor Item No.",FALSE,FALSE,''); // for text

EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;UnderLine : Boolean;NumberFormat : Text[30])

ExcelBuf.INIT;
ExcelBuf.VALIDATE("Row No.",RowNo);
ExcelBuf.VALIDATE("Column No.",ColumnNo);
ExcelBuf."Cell Value as Text" := CellValue;
ExcelBuf.Formula := '';
ExcelBuf.Bold := Bold;
ExcelBuf.Underline := UnderLine;
ExcelBuf.NumberFormat := NumberFormat;
ExcelBuf.INSERT;

This all works fine except:
This gives me the "The number in this cell is formatted as text or preceded by an apostrophe" message in Excel

Thanks
Steve

DaveT

Hi Steve,

EnterCell(RowNo,10,FORMAT("Outstanding Amount"),FALSE,FALSE,'@'); // for numbers

The @ sign tells Excel to format the cell as text - try a number format e.g. '0.00' for two decimal places. To see further examples - go to Excel and in Format Cells - look at the examples in the custom option.

DenSter

One tip that I found useful is when you want to accomplish something in Excel in C/AL code, is to turn on the macro recorder in Excel. Then do what you want to do, and look at the VBA that the macro recorder generated. Usually that translates almost directly into C/AL. I know formatting works that way, I've done something with that in the past.

SPost29

Thanks to both of you. That will do it.
Steve