Excel buffer error

kfuglsang

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.

kapamarou

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

Unfortunately, this did not resolve the issue.

Slawek_Guzek

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

Aske_Holst

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

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. ](*,)

Slawek_Guzek

Hi,

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

Regards,
Slawek

mootsoo

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?

Slawek_Guzek

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

abartonicek

Check your excel file, I got the same error when some fields had #NAME? value.
That obviously isn't real data

Filip_Crombez

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),'<',' ');