Cell widths when filling a cell using ADO

[Martin]

I am attemting t fill a spreadhseet with data using ADODB and OLEDB i.e using
"Provider=Microsoft.Jet.OLEDB.4.0;" "Extended Properties=Excel 8.0;";. I am
experiencing failures if I attempt to put more than 255 characters into a
field even though, as I undestand it, the last version of Excel to ahve a
limt of 255 was Excel 95. Is there any way to use adodb/oledb to ut >255
characaters into an Excel cell?

Thanks

[Rich]

Are you using sht.Range("A1).opyFromRecordset RS? Where RS is an ADODB
recordset populated with an ADODB command object. Or, are you writing data
from a database like MS Access using ADO? If you are copying bulk (detail)
data to an Excel Sheet from a database, I would use
sht.Range("A1").CopyFromRecordset RS which you can invoke from the database
(Access) using automation. If you are writing data from the database using
ADO, then try prepping your sheet first - make sure the respective receiving
cells are formatted for Text. If you are using ADO to write the data from
the database, I noticed that ADO is very touchy about cell formats. It will
complain at the drop of a hat. But still, ADO is the fastest way to
copy/write data from an external source to an Excel sheet.

Rich

"Martin" wrote:

> I am attemting t fill a spreadhseet with data using ADODB and OLEDB i.e using
> "Provider=Microsoft.Jet.OLEDB.4.0;" "Extended Properties=Excel 8.0;";. I am
> experiencing failures if I attempt to put more than 255 characters into a
> field even though, as I undestand it, the last version of Excel to ahve a
> limt of 255 was Excel 95. Is there any way to use adodb/oledb to ut >255
> characaters into an Excel cell?
>
> Thanks

[mklapp]

This 255 character limit seems to be a very hard limit when writing to an
Excel spreadsheet as I file. I am using OleDB Jet 4.0 provider and ADO.Net
and get this error when the data is more than 255. It is interesting to me
that this may be a known, if underreported, 'feature' in Excel. If one
searches for 'size limits' in the Excel Search For Help box, and select
"Excel Speciifications and Limits", we are informed that while a cell's
contents may be 32K characters long, a column may only be 255 characters
long. I understand the semantics, but try explaining it to a user.

mklapp

"Rich" wrote:

> Are you using sht.Range("A1).opyFromRecordset RS? Where RS is an ADODB
> recordset populated with an ADODB command object. Or, are you writing data
> from a database like MS Access using ADO? If you are copying bulk (detail)
> data to an Excel Sheet from a database, I would use
> sht.Range("A1").CopyFromRecordset RS which you can invoke from the database
> (Access) using automation. If you are writing data from the database using
> ADO, then try prepping your sheet first - make sure the respective receiving
> cells are formatted for Text. If you are using ADO to write the data from
> the database, I noticed that ADO is very touchy about cell formats. It will
> complain at the drop of a hat. But still, ADO is the fastest way to
> copy/write data from an external source to an Excel sheet.
>
> Rich
>
> "Martin" wrote:
>
> > I am attemting t fill a spreadhseet with data using ADODB and OLEDB i.e using
> > "Provider=Microsoft.Jet.OLEDB.4.0;" "Extended Properties=Excel 8.0;";. I am
> > experiencing failures if I attempt to put more than 255 characters into a
> > field even though, as I undestand it, the last version of Excel to ahve a
> > limt of 255 was Excel 95. Is there any way to use adodb/oledb to ut >255
> > characaters into an Excel cell?
> >
> > Thanks