busted workaround for 255 character limit in cells

[Guest]

Hello,

I recently encountered an annoying 'bug' in excel. While I can enter 32K
characters in a single cell, if I use oledb to write to and Excel
spreadsheet, the limit on character size is 255. This problem has been
reported in many different places.

Regardless, I still needed to write more than 255 characters
programmatically. I decided to use XML. Some preliminary testing
demonstrated that writing the constructed cell contents to an XML file, then
importing it to excel successfully brought in all the data.

Yahoo.

But then the typical 'gotcha' reared its most unattractive head.

Importing the XML file into Excel had the side-effect of sorting the XML
elements by Element name ?!?!!?

This is not acceptable.

How can I import an XML file into Excel XP and NOT have the columns sorted
on element name?

Mklapp

 

[Guest]

If you rename the .xml file to .txt and import it as a text file, you can
easily get more than 256 characters into each cell.

 

[Guest]

Thanks. I actually got it into the sheet by manually importing the XML file,
then deleting the schema data. A bit kludgy but it still works.

 

[Jamie Collins]

While I can enter 32K
characters in a single cell, if I use oledb to write to and Excel
spreadsheet, the limit on character size is 255.

Sub OleDbWrite32K()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = _
"Data Source=C:\Delme.xls;" & _
"Extended Properties='Excel 8.0'"
.Open
.Execute "CREATE TABLE Test (memo_col MEMO);"

Dim testValue As String
testValue = String$(32000, "a")

.Execute "INSERT INTO Test VALUES ('" & testValue & "');"

Dim rs As Object
Set rs = con.Execute( _
"SELECT LEN(memo_col) AS text_length FROM Test;")
End With

MsgBox rs(0).Name & vbTab & rs(0).Value
End Sub