Excel Automate and Number as Text '002333 = 002333

David_CoxDavid_Cox Member Posts: 509
This is one I have come up against and worked around, I do not have a need for it now but it is worth asking.

Sort Code = 002333 Excel Sheet Output = 2333

In excel if you were manually entering say bank sort codes or a product that started with a zero you could input '002333 displayed as 002333 the ' denotes it is number as text, but this does not work with the standard code for the Navision Excel Buffer it treats it as text '002333 and not number as text 002333, you cannot seem set the cell type, I have come up against this several times, I do not have the answer, but if anyone else does I would be interested as would others, for later use!

David
Analyst Developer with over 17 years Navision, Contract Status - Busy
Mobile: +44(0)7854 842801
Email: [email protected]
Twitter: https://twitter.com/Adeptris
Website: http://www.adeptris.com

Answers

  • Marcel_G.Marcel_G. Member Posts: 6
    Don't know if you mean this, or can use this for the Navision Excel Buffer..

    When you use in excel the formula =LEFT(A1;LEN(A1)) and A1 has the value '002333 then the ' will be ignored and the formulacel will contain 00233
    Perhaps you can use this formula as code for filling the Nav Excel Buffer?

    Marcel
  • PhennoPhenno Member Posts: 630
    David Cox wrote:
    This is one I have come up against and worked around, I do not have a need for it now but it is worth asking.

    Sort Code = 002333 Excel Sheet Output = 2333

    In excel if you were manually entering say bank sort codes or a product that started with a zero you could input '002333 displayed as 002333 the ' denotes it is number as text, but this does not work with the standard code for the Navision Excel Buffer it treats it as text '002333 and not number as text 002333, you cannot seem set the cell type, I have come up against this several times, I do not have the answer, but if anyone else does I would be interested as would others, for later use!

    David

    you have to format a column/cell as text before you enter values in cells.

    I forgot the syntax but it goes something like
    Sheet.Range('A:A').Format := '@';

    For format types you can open excel and check which are excel strings for that.

    for examle,
    text is '@'
    decimal number is '0,00'
    decimal number with dots on thousands '#.##0,00'
  • David_CoxDavid_Cox Member Posts: 509
    Phenno wrote:
    you have to format a column/cell as text before you enter values in cells.

    That's just it, you have to use a pre formatted spreadsheet or write lots of code, but what I want is to create a new sheet with dynamic formatting, using the Excel Buffer, and the standard functions.

    If I type in Excel '002332 I Get "Number as Text" 002332
    I want the Navision Buffer to Know that If I export a numeric Value with a leading Zero then it must treat this as "Number as Text" not Text or Numeric when creating the sheet, this may just be an oversight.

    I recently had to create a report for a Customer that exported bank details and vat numbers with several different country specific formats, some had leading Zero's, I had to deal with this by prefixing the value with a Tilde ~ then telling the customer they would have to open the spreadsheet, format the column as text, and removing the tilde, but this is hardly nice automation, the Excel Buffer functions should be a little bit more useful giving us the option to choose a format type.

    I could have used a dataport, fixed width, tab or csv format, but when you open these in excel it strips the leading zeros, export as txt, open excel and then open in file, this is the only option that gives you a chance to say this column is text to keep the leading zero format.
    Analyst Developer with over 17 years Navision, Contract Status - Busy
    Mobile: +44(0)7854 842801
    Email: [email protected]
    Twitter: https://twitter.com/Adeptris
    Website: http://www.adeptris.com
  • SavatageSavatage Member Posts: 7,142
    edited 2007-01-13
    Savatage wrote:
    But if the Zip code code starts with leading Zero's - the Zero don't get sent over - how do you force a "text format" to a field.
    And to answer the 2nd question: put a ' before your field so Excel treats it as text.
    EnterCell(Row, Column, '''' + "Ship-to ZIP Code", FALSE, FALSE, FALSE); 
    

    add the '''' + before the field (4 single quotes)

    or
    http://www.mibuso.com/forum/viewtopic.php?t=4664
    or
    http://www.mibuso.com/forum/viewtopic.php?t=15911
  • Luc_VanDyckLuc_VanDyck Member, Moderator, Administrator Posts: 3,633
    Savatage wrote:
    add the '''' + before the field
    Your recent aquired Excel automation knowledge come in handy ;-)
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • couberpucouberpu Member Posts: 317
    I will would just use custom format as following:

    WorkSheet.Range(<Cell>).NumberFormat := '000000;######';

    In this way, it would make sure the length is 6 and kept leading zeros. You can do this is excel cell format too.

    The downside of this way is the fixed length.
  • David_CoxDavid_Cox Member Posts: 509
    Savatage wrote:
    add the '''' + before the field
    Your recent aquired Excel automation knowledge come in handy ;-)

    I just tested it and it works fine, as text and formula, I am sure I did try this before and got the string, I must have put to many '''''' s, I know that 4 '''' is single quote and 6 '''''' is double quote (Speech Marks), oh well it might be useful for other searches!
    OBJECT Report 99999 Leading Zero to Excel
    {
      OBJECT-PROPERTIES
      {
        Date=13/01/07;
        Time=18:59:15;
        Modified=Yes;
        Version List=NAVW13.70,NAVGB3.70;
      }
      PROPERTIES
      {
        CaptionML=[ENU=Export Acc. Sched. to Excel;
                   ENG=Export Acc. Sched. to Excel];
        ProcessingOnly=Yes;
      }
      DATAITEMS
      {
        { PROPERTIES
          {
            DataItemTable=Table2000000026;
            DataItemTableView=SORTING(Number)
                              WHERE(Number=CONST(1));
            OnAfterGetRecord=VAR
                               Window@1000 : Dialog;
                               RecNo@1001 : Integer;
                               TotalRecNo@1002 : Integer;
                               RowNo@1003 : Integer;
                               ColumnNo@1004 : Integer;
                             BEGIN
                               Window.OPEN(
                                 Text000 +
                                 '@1@@@@@@@@@@@@@@@@@@@@@\');
                               Window.UPDATE(1,0);
                               TotalRecNo := 1;
                               RecNo :=0;
    
                               TempExcelBuffer.DELETEALL;
                               CLEAR(TempExcelBuffer);
    
    
                               RowNo := 1;
                               ColumnNo := 1;
                               EnterCell(RowNo,
                                         ColumnNo,
                                         STRSUBSTNO('%1','''')+'002332',
                                         FALSE,
                                         FALSE,
                                         FALSE
                                         );
    
    
                               ColumnNo := 2;
                               TempExcelBuffer.INIT;
                               TempExcelBuffer.VALIDATE("Row No.",RowNo);
                               TempExcelBuffer.VALIDATE("Column No.",ColumnNo);
                               TempExcelBuffer."Cell Value as Text" := '';
                               TempExcelBuffer.Formula := ''''+'002332';
                               TempExcelBuffer.Bold := FALSE;
                               TempExcelBuffer.Italic := FALSE;
                               TempExcelBuffer.Underline := FALSE;
                               TempExcelBuffer.INSERT;
    
    
                               Window.CLOSE;
    
                               IF Option = Option::"Update Workbook" THEN BEGIN
                                 TempExcelBuffer.OpenBook(FileName,SheetName);
                                 TempExcelBuffer.CreateSheet(SheetName,'',COMPANYNAME,USERID);
                               END ELSE BEGIN
                                 TempExcelBuffer.CreateBook;
                                 TempExcelBuffer.CreateSheet('Test Zero','',COMPANYNAME,USERID);
                               END;
                               TempExcelBuffer.GiveUserControl;
                             END;
    
          }
          SECTIONS
          {
          }
           }
      }
      REQUESTFORM
      {
        PROPERTIES
        {
          Width=8910;
          Height=1540;
          SaveValues=Yes;
          OnOpenForm=BEGIN
                       UpdateRequestForm;
                     END;
    
        }
        CONTROLS
        {
          { 1   ;TextBox      ;3410 ;550  ;5500 ;440  ;Name=FileName;
                                                       CaptionML=[ENU=Workbook File Name;
                                                                  ENG=Workbook File Name];
                                                       SourceExpr=FileName;
                                                       OnAssistEdit=VAR
                                                                      CommonDialogMgt@1001 : Codeunit 412;
                                                                    BEGIN
                                                                      FileName := CommonDialogMgt.OpenFile(Text002,FileName,2,'',0);
                                                                    END;
                                                                     }
          { 4   ;Label        ;0    ;550  ;3300 ;440  ;ParentControl=1 }
          { 5   ;TextBox      ;3410 ;1100 ;5500 ;440  ;Name=SheetName;
                                                       CaptionML=[ENU=Worksheet Name;
                                                                  ENG=Worksheet Name];
                                                       SourceExpr=SheetName;
                                                       OnAssistEdit=VAR
                                                                      ExcelBuf@1001 : Record 370;
                                                                    BEGIN
                                                                      SheetName := ExcelBuf.SelectSheetsName(FileName);
                                                                    END;
                                                                     }
          { 6   ;Label        ;0    ;1100 ;3300 ;440  ;ParentControl=5 }
          { 7   ;TextBox      ;3410 ;0    ;2750 ;440  ;CaptionML=[ENU=Option;
                                                                  ENG=Option];
                                                       OptionCaptionML=[ENU=Create Workbook,Update Workbook;
                                                                        ENG=Create Workbook,Update Workbook];
                                                       SourceExpr=Option;
                                                       OnValidate=BEGIN
                                                                    UpdateRequestForm;
                                                                  END;
                                                                   }
          { 8   ;Label        ;0    ;0    ;3300 ;440  ;ParentControl=7 }
        }
      }
      CODE
      {
        VAR
          Text000@1000 : TextConst 'ENU=Analyzing Data...\\;ENG=Analyzing Data...\\';
          Text001@1001 : TextConst 'ENU=Filters;ENG=Filters';
          Text002@1002 : TextConst 'ENU=Update Workbook;ENG=Update Workbook';
          TempExcelBuffer@1006 : TEMPORARY Record 370;
          FileName@1010 : Text[250];
          SheetName@1011 : Text[250];
          Option@1012 : 'Create Workbook,Update Workbook';
    
        PROCEDURE UpdateRequestForm@1();
        BEGIN
          IF Option = Option::"Update Workbook" THEN BEGIN
            RequestOptionsForm.FileName.ENABLED(TRUE);
            RequestOptionsForm.SheetName.ENABLED(TRUE);
          END ELSE BEGIN
            FileName := '';
            SheetName := '';
            RequestOptionsForm.FileName.ENABLED(FALSE);
            RequestOptionsForm.SheetName.ENABLED(FALSE);
          END;
        END;
    
        LOCAL PROCEDURE EnterCell@2(RowNo@1000 : Integer;ColumnNo@1001 : Integer;CellValue@1002 : Text[250];Bold@1003 : Boolean;Italic@1004 : Boolean;UnderLine@1005 : Boolean);
        BEGIN
          TempExcelBuffer.INIT;
          TempExcelBuffer.VALIDATE("Row No.",RowNo);
          TempExcelBuffer.VALIDATE("Column No.",ColumnNo);
          TempExcelBuffer."Cell Value as Text" := CellValue;
          TempExcelBuffer.Formula := '';
          TempExcelBuffer.Bold := Bold;
          TempExcelBuffer.Italic := Italic;
          TempExcelBuffer.Underline := UnderLine;
          TempExcelBuffer.INSERT;
        END;
    
        BEGIN
        END.
      }
    }
    
    
    Analyst Developer with over 17 years Navision, Contract Status - Busy
    Mobile: +44(0)7854 842801
    Email: [email protected]
    Twitter: https://twitter.com/Adeptris
    Website: http://www.adeptris.com
  • PhennoPhenno Member Posts: 630
    David Cox wrote:
    Phenno wrote:
    you have to format a column/cell as text before you enter values in cells.

    That's just it, you have to use a pre formatted spreadsheet or write lots of code, but what I want is to create a new sheet with dynamic formatting, using the Excel Buffer, and the standard functions.

    Indeed I rarely use excel buffer funcionality but it should not be a problem to add the code in eb, to add functionality such as preformating excel columns for you before entering data so then, you will be able to use standard functions and excel buffer And few lines of code.

    Or at least to fix a problem with leading zeroes if that kind of funcionality already exisist.
  • jarodjarod Member Posts: 1
    Hi all,

    I am trying to import journal lines from Excel :
    CASE lrs_Excel."Column No." OF
           1: rs_GenJnlLine.VALIDATE("Account No.",lrs_Excel."Cell Value as Text");
    

    but NAV imports G/L accounts as numbers :
    Account # in Excel > 10000
    Account # in Excel Buffer > 10 000

    I can't find how I can use NumberFormat fonction with this ?
    Any idea would be great... =D>

    Thanks in advance for help !
    Jarod
Sign In or Register to comment.