Excel buff Want to export numbers as numbers in Excel

SPost29
SPost29 Member Posts: 148
edited 2010-03-08 in NAV/Navision Classic Client
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

Answers

  • DaveT
    DaveT Member Posts: 1,039
    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
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
  • SPost29
    SPost29 Member Posts: 148
    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
    DaveT Member Posts: 1,039
    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.
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
  • DenSter
    DenSter Member Posts: 8,307
    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.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • SPost29
    SPost29 Member Posts: 148
    Thanks to both of you. That will do it.
    Steve

Categories