Use ADO in VB to read excel file problem

[Guest]

Hi all,

I have written a VB program to read the excel file via ADO.
ie :
rs.Open "SELECT * FROM [sheet1$]"

However, I find that if the excel cell contains more that 255 characters,
the recordset cannot retrieve it correctly, it will store the first 255
character and discard the rest.

Is it a ADO Excel provider limitation ?
anyone idea to solve this problem ?

Thanks

 

[Guest]

Hi
A cell can contain up to 256 chrs. If you are reading 255 the VB control you
are using is probally set to o index. Most VB controls can be set to either 0
or 1 as the starting index number.

Goog Luck
TK

 

[Tom Ogilvy]

A cell can contain approximately 32K characters in xl97 and later.

--
Regards,
Tom Ogilvy

Hi
A cell can contain up to 256 chrs. If you are reading 255 the VB control you
are using is probally set to o index. Most VB controls can be set to either 0
or 1 as the starting index number.

Goog Luck
TK

Hi all,

I have written a VB program to read the excel file via ADO.
ie :
rs.Open "SELECT * FROM [sheet1$]"

However, I find that if the excel cell contains more that 255 characters,
the recordset cannot retrieve it correctly, it will store the first 255
character and discard the rest.

Is it a ADO Excel provider limitation ?
anyone idea to solve this problem ?

Thanks

 

[Guest]

I assumed "ong" was using an older version and was having trouble returning
the last character. I stand corrected or amended.

Good Luck
TK

A cell can contain approximately 32K characters in xl97 and later.

--
Regards,
Tom Ogilvy

Hi
A cell can contain up to 256 chrs. If you are reading 255 the VB control you
are using is probally set to o index. Most VB controls can be set to either 0
or 1 as the starting index number.

Goog Luck
TK

Hi all,

I have written a VB program to read the excel file via ADO.
ie :
rs.Open "SELECT * FROM [sheet1$]"

However, I find that if the excel cell contains more that 255 characters,
the recordset cannot retrieve it correctly, it will store the first 255
character and discard the rest.

Is it a ADO Excel provider limitation ?
anyone idea to solve this problem ?

Thanks

 

[Jamie Collins]

I have written a VB program to read the excel file via ADO.
ie :
rs.Open "SELECT * FROM [sheet1$]"

However, I find that if the excel cell contains more that 255
characters,
the recordset cannot retrieve it correctly, it will store the first 255
character and discard the rest.

Is it a ADO Excel provider limitation ?
anyone idea to solve this problem ?

Sounds like Jet is determining the column's data type as 'text' rather
than 'memo'. For details see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--

 

[Robin Hammond]

While the 255 limit is up for discussion, why is there still a limt of 255
characters when copying a sheet?

I found this today in XP and just tried it in 2003 and am somewhat surprised
given that the character limit has gone up over the years to find that I
can't copy a sheet without losing data. I've got an easy workaround, but
surely this is a legacy they should have been fixed?

Robin Hammond
www.enhanceddatasystems.com

I have written a VB program to read the excel file via ADO.
ie :
rs.Open "SELECT * FROM [sheet1$]"

However, I find that if the excel cell contains more that 255
characters,
the recordset cannot retrieve it correctly, it will store the first 255
character and discard the rest.

Is it a ADO Excel provider limitation ?
anyone idea to solve this problem ?

Sounds like Jet is determining the column's data type as 'text' rather
than 'memo'. For details see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--