Excel buffer error

kfuglsang
kfuglsang Member Posts: 26
edited 2012-02-29 in NAV/Navision Classic Client
Hi all,

I'm starting to use the Excel Buffer to read a rather large worksheet into the Excel Buffer table.

I am using the ReadSheet() function of the Excel Buffer, but I have a problem when a cell contains a formula with an error. E.g. #DIV/0. When the function reaches such a cell it crashes with an error:
This datatype is not supported by C/SIDE. The following datatypes are supported: VT_VOID, VT_I2, VT_I4, ..., VT_BOOL

In the line: 
"Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');

Any ideas how I can make it skip cells containing errors?

Thanks in advance.

Comments

  • kapamarou
    kapamarou Member Posts: 1,152
    I've seen this error in word automation, so give it a go. It might help.


    Declare a Text variable, assign to it your value:
    MyText := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');


    Then use that variable:

    "Cell Value as Text" := MyText;
  • kfuglsang
    kfuglsang Member Posts: 26
    Unfortunately, this did not resolve the issue.
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    Hi,

    The NAV gives you this error sometimes when you try to read a cell containing formuals, or advanced formating (like conditional formating, advanced number/data formating), or generally containing any stuff beyond simple numbers in simple formatted cells.

    The workaround is to copy all the excel sheet into new file, paste as values, and then try to read it.

    Regards,
    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Aske_Holst
    Aske_Holst Member Posts: 13
    Another solution can be to read the cell values using the Text property: 
    "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Text),'<',' ');
    This is, as far as I know, also the only way to get to the cached value in linked fields.
  • mootsoo
    mootsoo Member Posts: 70
    Hi all
    I have one problem on Excel buffer table.
    There is a form, which has report data. That form has exporting data to excel, and using excel buffer table.
    But result is a wrong. When i'm debugging, then error occures on excel buffer table (#370)

    Column No. - OnValidate() 
    xlColID := '';
IF "Column No." <> 0 THEN BEGIN
  x := "Column No." - 1;
  c := 65 + x MOD 26;
  xlColID[10] := c;
  i := 10;
  WHILE x > 25 DO BEGIN
    x := x DIV 26;
    i := i - 1;
    c := 64 + x MOD 26;
    xlColID[i] := c;
  END;
  FOR x := i TO 10 DO
    xlColID[1+x-i] := xlColID[x];
END;

    Error occures all these lines. But not always. If i want to export about 10 lines. Then It's ok.
    But more than... example 49 lines is coming 600 lines in excel.
    I have checked all lines and did some mathematics. But it repeating not regular.
    First 32 lines of 49. Then repeating 25 lines for 109 times.. etc
    But i believe in microsoft, and where could it be? I have checked all loops. ](*,)
    bye my work, bye navision
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    Hi,

    Check if you are using A1 or W1C1 referencing style in your Excel config.

    Regards,
    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • mootsoo
    mootsoo Member Posts: 70
    Slawek Guzek wrote:
    Hi,

    Check if you are using A1 or W1C1 referencing style in your Excel config.

    Regards,
    Slawek
    hi
    what is the A1 and W1C1?
    bye my work, bye navision
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    Hi

    Sorry, I meant R1C1 style, not W1C1.

    A1 referencing style or R1C1 referencing style are two different method of addressing cells in Excell worksheets. The default is A1, but can be changed to R1C1 in Excel Setup

    A1 means thar columns are addresed by letters A, B, C.. and so on, and rows by numbers. R1C1 means that columns are addressed by C1, C2, C3, and rows by R1, R2, R3 and so on. Cell address A1 = R1C1, A2 = R2C1, B1 = R1C2, etc.

    NAV assumes that you're using A1 style and the trigger calculates A1 style address based on actual row and column number.

    Anyway after re-rerading your email I think that referencing style is irrelevent to your problem. Sorry for confusion.

    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • abartonicek
    abartonicek Member Posts: 162
    Check your excel file, I got the same error when some fields had #NAME? value.
    That obviously isn't real data :)
    Better to be critical then self-critical :)
  • Filip_Crombez
    Filip_Crombez Member Posts: 13
    I experienced the same problem, when for example references in excel have been deleted (+#VERW! or +#REF!)
    I solved it in this way :

    VariantValue := XlWrkSht.Range(xlColID + xlRowID).Value;
    IF VariantValue.ISBOOLEAN OR
    VariantValue.ISOPTION OR
    VariantValue.ISINTEGER OR
    VariantValue.ISDECIMAL OR
    VariantValue.ISCHAR OR
    VariantValue.ISTEXT OR
    VariantValue.ISCODE OR
    VariantValue.ISDATE OR
    VariantValue.ISTIME THEN
    "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' ');

Categories