Excel Automate and Number as Text '002333 = 002333

David_Cox · 2007-01-13

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

Marcel_G. · 2007-01-13

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

Phenno · 2007-01-13

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_Cox · 2007-01-13

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.

Savatage · 2007-01-13

Luc Van Dyck wrote:
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_VanDyck · 2007-01-13

Savatage wrote:

add the '''' + before the field

Your recent aquired Excel automation knowledge come in handy ;-)

couberpu · 2007-01-13

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_Cox · 2007-01-13

Luc Van Dyck wrote:

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.
  }
}

Phenno · 2007-01-13

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.

jarod · 2008-02-23

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

Howdy, Stranger!

Quick Links

Categories

Excel Automate and Number as Text '002333 = 002333

Answers