Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workaround for HYPERLINK argument length limit | Excel Worksheet Functions | |||
Excel text export limit - 1024 per line (not cell), workaround? | Excel Discussion (Misc queries) | |||
Need to set character limit in cells of a worksheet. | Excel Worksheet Functions | |||
workaround for problem hyperlinks in Excel (doesn't work if pathcontains # character) | Excel Worksheet Functions | |||
Is there a 255 character limit in pivot table cells? | Excel Worksheet Functions |