Range.Value2 is failing for lenghty strings

[Naresh Mirkhelkar]

Hi,
I am trying to assign values to range using an array, however, it is
failing when one of the cell values are going beyone 930+ chars.
I have the below code trying to assign an DataTable itemarray to the range...

//insertRange is an Excel.Range object
insertRange.Value2 = _myDataTable.Rows.ItemArray;


The above code is working fine as long as the itemArray contains values less
than 930 characters ( I have not really arrived at the number exactly when it
is failing). Can you please let me know why Value2 property is failing in
this long string case?
or how can I set the length of the cell/listColumn?
Please help.
Thanks,
Naresh

 

[NickHK]

Naresh,
XL2K, this works:
ActiveCell.Value2 = String(1000, "c")
MsgBox Len(ActiveCell.Value2)

Maybe the problem is with the source of your data rather than Excel ?

NickHK

 

[Naresh Mirkhelkar]

Hi Nick,
It is working when Data is provided for individual cells, but not when data
is set to the Range object directly as mentioned in my mail below.
Thanks,
Naresh

Naresh,
XL2K, this works:
ActiveCell.Value2 = String(1000, "c")
MsgBox Len(ActiveCell.Value2)

Maybe the problem is with the source of your data rather than Excel ?

NickHK

Hi,
I am trying to assign values to range using an array, however, it is
failing when one of the cell values are going beyone 930+ chars.
I have the below code trying to assign an DataTable itemarray to the range...

//insertRange is an Excel.Range object
insertRange.Value2 = _myDataTable.Rows.ItemArray;


The above code is working fine as long as the itemArray contains values less
than 930 characters ( I have not really arrived at the number exactly when it
is failing). Can you please let me know why Value2 property is failing in
this long string case?
or how can I set the length of the cell/listColumn?
Please help.
Thanks,
Naresh

 

[NickHK]

Naresh,
Still works
Set rng = ActiveCell
rng.Value2 = String(1000, "c")


NickHK

Hi Nick,
It is working when Data is provided for individual cells, but not when data
is set to the Range object directly as mentioned in my mail below.
Thanks,
Naresh

Naresh,
XL2K, this works:
ActiveCell.Value2 = String(1000, "c")
MsgBox Len(ActiveCell.Value2)

Maybe the problem is with the source of your data rather than Excel ?

NickHK

message news:[email protected]...

Hi,
I am trying to assign values to range using an array, however, it is
failing when one of the cell values are going beyone 930+ chars.
I have the below code trying to assign an DataTable itemarray to the range...

//insertRange is an Excel.Range object
insertRange.Value2 = _myDataTable.Rows.ItemArray;


The above code is working fine as long as the itemArray contains

values
less

than 930 characters ( I have not really arrived at the number exactly

when
it

is failing). Can you please let me know why Value2 property is failing in
this long string case?
or how can I set the length of the cell/listColumn?
Please help.
Thanks,
Naresh

 

[Naresh Mirkhelkar]

Hi Nick,
In your case, the range is still containing only one cell. In my case,
Range contains 4 columns (cells). I tried the way you have described, but it
worked only when I assigned values to individual cells as below...

object[] rowData = _myDataTable.Rows.ItemArray;
for (int k=0; k < rowData.Length ; k++{
insertRange.Cells[1,k+1] = rowData[k]
}

....and also doing the above way is affecting performance.

Thank you for extending your help.

Thanks,
Naresh

Naresh,
Still works
Set rng = ActiveCell
rng.Value2 = String(1000, "c")


NickHK

Hi Nick,
It is working when Data is provided for individual cells, but not when data
is set to the Range object directly as mentioned in my mail below.
Thanks,
Naresh

Naresh,
XL2K, this works:
ActiveCell.Value2 = String(1000, "c")
MsgBox Len(ActiveCell.Value2)

Maybe the problem is with the source of your data rather than Excel ?

NickHK

message Hi,
I am trying to assign values to range using an array, however, it is
failing when one of the cell values are going beyone 930+ chars.
I have the below code trying to assign an DataTable itemarray to the
range...

//insertRange is an Excel.Range object
insertRange.Value2 = _myDataTable.Rows.ItemArray;


The above code is working fine as long as the itemArray contains values
less
than 930 characters ( I have not really arrived at the number exactly when
it
is failing). Can you please let me know why Value2 property is failing in
this long string case?
or how can I set the length of the cell/listColumn?
Please help.
Thanks,
Naresh