Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
Hi,
Can anybody help me to overcome this problem? ADODB is not allowing me to copy more than 255 characters into an excel cell. I must truncate the string to 255 characters before updating. otherwise it is throwing exception and fails. Please check the code below. Some times the string length is more than 255 characters. Thannks. XLSConn = New ADODB.Connection XLSConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strDestination & _ ";Extended Properties=""Excel 8.0;HDR=NO;""") Dim source As String Dim arrData Dim Counter As Integer source = "Select * from [" & sheetName & "$" & strRecRange & "]" XLSrs.Open(source, XLSConn, 1, 3) arrData = Split(strRec, Chr(9)) For Counter = LBound(arrData) To UBound(arrData) - 1 XLSrs.Fields(Counter).Value = Left(arrData(Counter), 255) Next XLSrs.Update() XLSrs.Close() |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
;Extended Properties=""Excel 8.0;HDR=NO;""")
What is Excel 8.0? Is that Excel 97? I know that in previous versions cell text lengths were limited to 255 characters. Now they accept ~32k characters. But I'm not sure which version it was when then length was increased. Also, in newer versions of Excel, cells with long strings will not always display correctly. Even if you widen the row height and column width the data gets truncated, even though it's all still there in memory. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
The cell limit on strings was raised in xl97 (version 8.0)
-- Regards, Tom Ogilvy "Nick Hebb" wrote in message oups.com... ;Extended Properties=""Excel 8.0;HDR=NO;""") What is Excel 8.0? Is that Excel 97? I know that in previous versions cell text lengths were limited to 255 characters. Now they accept ~32k characters. But I'm not sure which version it was when then length was increased. Also, in newer versions of Excel, cells with long strings will not always display correctly. Even if you widen the row height and column width the data gets truncated, even though it's all still there in memory. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
KK
i think your observation is correct. In the connection string you can set the extended property IMEX=1 to force mixed data types to text. That's the only way ADO/JET can READ excel cells beyond the 255 chars. However when you use the IMEX parameter (regardless if you set it to 0 or 1... the recordset will be non updatable... I've not yet come across a solution to write long strings to excel files with Jet... -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam KK wrote : Hi, Can anybody help me to overcome this problem? ADODB is not allowing me to copy more than 255 characters into an excel cell. I must truncate the string to 255 characters before updating. otherwise it is throwing exception and fails. Please check the code below. Some times the string length is more than 255 characters. Thannks. XLSConn = New ADODB.Connection XLSConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strDestination & _ ";Extended Properties=""Excel 8.0;HDR=NO;""") Dim source As String Dim arrData Dim Counter As Integer source = "Select * from [" & sheetName & "$" & strRecRange & "]" XLSrs.Open(source, XLSConn, 1, 3) arrData = Split(strRec, Chr(9)) For Counter = LBound(arrData) To UBound(arrData) - 1 XLSrs.Fields(Counter).Value = Left(arrData(Counter), 255) Next XLSrs.Update() XLSrs.Close() |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
Hi
The IMEX=1 property doesn't work in the above case. Any ideas? Thanks. KK |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
The cell limit on strings was raised in xl97 (version 8.0)
I was wondering about that because now I remember creating a request form in Excel97 that users would fill in and email to my department. One field was used to enter comments / descriptions, and users sometimes wrote very long passages. The entire company had Excel 97, but some were only able to enter 255 characters. It was really quirky. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
Doh! I just re-read the OP's post and realized you're reading values
*from* Excel not writing *to* Excel. Please disregard my comments. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
Nick,
I am writing into excel using these statements XLSrs.Update() XLSrs.Close() Thanks. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
ADODB is not allowing me to copy more than 255 characters into an excel
cell. I think he is maybe doing both. -- Regards, Tom Ogilvy "Nick Hebb" wrote in message oups.com... Doh! I just re-read the OP's post and realized you're reading values *from* Excel not writing *to* Excel. Please disregard my comments. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
keepITcool wrote:
In the connection string you can set the extended property IMEX=1 to force mixed data types to text. That's the only way ADO/JET can READ excel cells beyond the 255 chars. However when you use the IMEX parameter (regardless if you set it to 0 or 1... the recordset will be non updatable... I've not yet come across a solution to write long strings to excel files with Jet... Jet's VARCHAR (a.k.a TEXT) data type is limited to 255 by design e.g. CREATE TABLE [Excel 8.0;Database=C:\Test57.xls;].TestText (data_col VARCHAR(544)); generates the error "Size of field 'data_col' is too long." The trick is to type the Excel table as MEMO e.g. CREATE TABLE [Excel 8.0;Database=C:\Test57.xls;].TestMemo (data_col MEMO) ; INSERT INTO [Excel 8.0;Database=C:\Test57.xls;].TestMemo (data_col) VALUES ('thought that bell was in my dream all in my head until the trucks were in the yard and the fire was in my bed oh can you hear the ringing bell telling time like time was to tell can you see the smoke rise and curl all the way from your side of my world hush your talk here comes the boss down off of his perch he walks the floor in cream white shoes like we were piggin iron in church oh can you hear the furnace hum above the shouts and all the chewing gum hear the union priest lead the factory choirgirls singing out to your side of my world') ; SELECT data_col, LEN(data_col) AS length, TYPENAME(data_col) AS type FROM [Excel 8.0;Database=C:\Test57.xls;].TestMemo ; Unfortunately, Jet does not support CAST, from the ANSI SQL standards. Instead, it uses the VBA functions such as Clng, CCur and CDate to convert values between data types but provides no effective mapping between CStr and MEMO (see the TYPENAME result in the query above). The only way I can think to coerce a non-MEMO Excel column to read more than 255 characters is make it the majority type for the rows used according to the TypeGuessRows setting e.g. put a 255 string in row 1 and set TypeGuessRows to 1. There's some notes he http://www.dicks-blog.com/archives/2...ed-data-types/ Jamie. -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
Jamie..
I thought you'd react :) Tried your suggestion...some changes but no luck. File: some cells in top row in file contains long strings. Registry: ImportMixedTypes=Text TypeGuessRows = 1 Connectstring: HDR=NO;IMEX not set. The fields in the recordset where row1 contains text255chars are now 'correctly' typed as: adLongVarWchar (203) which corresponds with MEMO in SQL ddl and reading long strings is no problem. However when attempting an update the previous error message changes. From a 'recordset not updateble' (as you would with IMEX) I now get 'field not updateble'.. So I checked Field attributes a "text" gives FieldAttributes: 104 a "memo" gives FieldAttributes: 234 So it appears that only adFldLong x80 (long binary data) has been set for the memo. adFldUnknownUpdatable(0x8) is set in both cases as adFldUpdateble (0x4) remains off. no go so far.... -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jamie Collins wrote : keepITcool wrote: In the connection string you can set the extended property IMEX=1 to force mixed data types to text. That's the only way ADO/JET can READ excel cells beyond the 255 chars. However when you use the IMEX parameter (regardless if you set it to 0 or 1... the recordset will be non updatable... I've not yet come across a solution to write long strings to excel files with Jet... Jet's VARCHAR (a.k.a TEXT) data type is limited to 255 by design e.g. CREATE TABLE [Excel 8.0;Database=C:\Test57.xls;].TestText (data_col VARCHAR(544)); generates the error "Size of field 'data_col' is too long." The trick is to type the Excel table as MEMO e.g. CREATE TABLE [Excel 8.0;Database=C:\Test57.xls;].TestMemo (data_col MEMO) ; INSERT INTO [Excel 8.0;Database=C:\Test57.xls;].TestMemo (data_col) VALUES ('thought that bell was in my dream all in my head until the trucks were in the yard and the fire was in my bed oh can you hear the ringing bell telling time like time was to tell can you see the smoke rise and curl all the way from your side of my world hush your talk here comes the boss down off of his perch he walks the floor in cream white shoes like we were piggin iron in church oh can you hear the furnace hum above the shouts and all the chewing gum hear the union priest lead the factory choirgirls singing out to your side of my world') ; SELECT data_col, LEN(data_col) AS length, TYPENAME(data_col) AS type FROM [Excel 8.0;Database=C:\Test57.xls;].TestMemo ; Unfortunately, Jet does not support CAST, from the ANSI SQL standards. Instead, it uses the VBA functions such as Clng, CCur and CDate to convert values between data types but provides no effective mapping between CStr and MEMO (see the TYPENAME result in the query above). The only way I can think to coerce a non-MEMO Excel column to read more than 255 characters is make it the majority type for the rows used according to the TypeGuessRows setting e.g. put a 255 string in row 1 and set TypeGuessRows to 1. There's some notes he http://www.dicks-blog.com/archives/2...ata-mixed-data -types/ Jamie. -- |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
keepITcool wrote: Tried your suggestion...some changes but no luck. Me neither. I generated the most preposterous Jet error there is: UPDATE [Excel 8.0;HDR=NO;IMEX=1;Database=C:\Test69.xls;].[TestMemo$A3:A3] SET F1 = 'i could dance when i was young and i was pretty good id do all the tricks and such but back then everybody could i should have seen how this would be but nothings true till ive seen it on tv yet there you were in your high heels and curls coming in as big as life from your side of my world lets pretend weve never loved lets pretend our hands are clean free of all the spit and shine and the smell of gasoline cause here come the planes and the tambourines the funeral march and the beauty queens the circus freaks selling lemonade from the back of an open air motorcade here come the heart machines and the baby shoes the ship to shore relay of the sporting news that mail order brides fake bleeps and pearls all making way from your side of my world'; "Operation must use an updateable query." Huh? I'm not using a query! Jamie. -- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
you are :) = Structured QUERY language.
-- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jamie Collins wrote : Huh? I'm not using a query! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
Roedd <<Jamie Collins wedi ysgrifennu:
UPDATE [Excel 8.0;HDR=NO;IMEX=1;Database=C:\Test69.xls;].[TestMemo$A3:A3] SET F1 = 'i could dance when i was young and i was pretty good id do all the tricks and such but back then everybody could i should have seen how this would be but nothings true till ive seen it on tv yet there you were in your high heels and curls coming in as big as life from your side of my world lets pretend weve never loved lets pretend our hands are clean free of all the spit and shine and the smell of gasoline cause here come the planes and the tambourines the funeral march and the beauty queens the circus freaks selling lemonade from the back of an open air motorcade here come the heart machines and the baby shoes the ship to shore relay of the sporting news that mail order brides fake bleeps and pearls all making way from your side of my world'; Just out of interest, this looks like an extract from a spam email designed to confuse scanners. Are you designing a spamming application using Excel? As I said, just out of interest. -- Rob http://www.asta51.dsl.pipex.com/webcam/ This message is copyright Robert Bruce and intended for distribution only via NNTP. Dissemination via third party Web forums with the exception of Google Groups and Microsoft Communities is strictly prohibited and may result in legal action. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
Robert Bruce wrote: <<Joe Henry lyric snipped Just out of interest, this looks like an extract from a spam email designed to confuse scanners. Are you designing a spamming application using Excel? Say it ain't so, Joe <g. Nothing that sinister. I was looking for a relatively long piece of code and grabbed the lyrics of the song I was listening to. Yaki-Da, Jamie. -- |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
keepITcool wrote:
Huh? I'm not using a query! you are :) = Structured QUERY language. I think not <g. SQL is not an acronym. From the horse's mouth: "SQL, spelt out as letters when pronounced, is the ISO standard database language and it has no official meaning" http://groups-beta.google.com/group/...d7ceaa753eabc4 I like this one too: "We used to joke that SQL stood for "Scarcely Qualifies as a Language" because it has no I/O and can't format output. Its math library is limited because it isn't a computational language. It doesn't do text searching, list processing, or graphics. The only purposes of SQL are data management and retrieval. Period." http://www.intelligententerprise.com...cleID=50500830 Jamie. -- |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
a horse's mouth indeed... and UTTER bull****. I quote: By the end of the System/R project, IBM had implemented a language that supported System/R's multi-table queries and multiple-user access called the Structured English Query Language (SEQUEL). The name later was shortened to Structured Query Language (SQL). Today, we still pronounce the abbreviation as "sequel" because of these early roots. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Jamie Collins wrote : keepITcool wrote: Huh? I'm not using a query! you are :) = Structured QUERY language. I think not <g. SQL is not an acronym. From the horse's mouth: "SQL, spelt out as letters when pronounced, is the ISO standard database language and it has no official meaning" http://groups-beta.google.com/group/...server.program ming/msg/28d7ceaa753eabc4 I like this one too: "We used to joke that SQL stood for "Scarcely Qualifies as a Language" because it has no I/O and can't format output. Its math library is limited because it isn't a computational language. It doesn't do text searching, list processing, or graphics. The only purposes of SQL are data management and retrieval. Period." http://www.intelligententerprise.com...rticleID=50500 830 Jamie. -- |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB is unable to copy more than 255 chars in excel cell
Roedd <<Jamie Collins wedi ysgrifennu:
Say it ain't so, Joe <g. Nothing that sinister. I was looking for a relatively long piece of code and grabbed the lyrics of the song I was listening to. I admit that I have never heard of Joe Henry. Maybe I ought to give him a chance after such an insult. I normally only listen to seventies dub reggae while working. Yaki-Da, Ac iechyd da i chi! -- Rob http://www.asta51.dsl.pipex.com/webcam/ This message is copyright Robert Bruce and intended for distribution only via NNTP. Dissemination via third party Web forums with the exception of Google Groups and Microsoft Communities is strictly prohibited and may result in legal action. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to copy cell reference | Excel Discussion (Misc queries) | |||
Anyone notice excel search within cell limited to first 1024 chars | Excel Discussion (Misc queries) | |||
ADODB is unable to copy more than 255 chars in excel cell | Excel Programming | |||
Copy sheets with more than 255 chars in a cell? | Excel Worksheet Functions | |||
copy cells with more then 255 chars... | Excel Programming |