Unanswered: VB help required with cell containing more than 255 characters
I'm just setting out using code really using I am using office 2003, I have come across a situation where I don’t know how to get around , the code below extracts data from a table held within an access database, and places it into an excel spreadsheet for further analysis, the “Details_of_Escalation” field Data Type is set to Memo, a user has written war and peace on a particular record, unfortunately when I try to run the code it bugs out at this point excess of 255 characters, is there away around this ?
This is the code I have put together so far, and it breaks at “Sheet4.Cells(j, i + 1) = rs(i)” part of the code
Dim strConnection, conn As ADODB.Connection, rs As ADODB.Recordset, strSQL As String, tmpArray() As Variant
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\Documents and Settings\mkw\My Documents\Database.mdb"
Set conn = CreateObject("ADODB.Connection")
To test your data, I would start with this to eliminate the error:
Sheet4.Cells(j, i + 1) = Trim(Mid(rs(i), 1, 32767))
32,767 characters appears to me to be the cell limit in Excel 2003.
Then analyze your data to see if the field or fields that have a huge
amount of text is something you want to keep and divide over as
many columns as it takes.
If you are going to keep all the text, you could write a function to
divide up the lengthy text into separate cells, with a text length that you
will choose, and also use the function to determine the last whole
word of each cell's text so that you do not split words between cells.