+ Reply to Thread
Results 1 to 4 of 4

busted workaround for 255 character limit in cells

  1. #1
    mklapp
    Guest

    busted workaround for 255 character limit in cells

    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


  2. #2
    Gary's Student
    Guest

    RE: busted workaround for 255 character limit in cells

    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.
    --
    Gary's Student


    "mklapp" wrote:

    > 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
    >


  3. #3
    mklapp
    Guest

    RE: busted workaround for 255 character limit in cells

    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.



    "Gary's Student" wrote:

    > 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.
    > --
    > Gary's Student
    >
    >
    > "mklapp" wrote:
    >
    > > 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
    > >


  4. #4
    Jamie Collins
    Guest

    Re: busted workaround for 255 character limit in cells



    mklapp wrote:

    > 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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1