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

Sub GetData()

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")
conn.Open strConnection

Set rs = CreateObject("ADODB.recordset")
strSQL = "SELECT [Escalation Log].Reference_No, [Escalation Log].Analyst, [Escalation Log].Submitted_by, [Escalation Log].Date, " & _
"[Escalation Log].Customer_Name, [Escalation Log].Customer_Site_Account_No, [Escalation Log].Customer_Contact, " & _
"[Escalation Log].Customer_Email, [Escalation Log].Customer_Tel, [Escalation Log].[Issue Title], " & _
"[Escalation Log].Details_of_Escalation, [Escalation Log].Impact, [Escalation Log].Reason_Code, " & _
"[Escalation Log].Req_Resolution_Date, [Escalation Log].Resolution_Owner, [Escalation Log].Resolution_Owner_Accepted, " & _
"[Escalation Log].Resolution, [Escalation Log].Status, [Escalation Log].Respond_To FROM [Escalation Log]"

rs.Open strSQL, conn

j = 1
Do Until rs.EOF = True
For i = 0 To 18
Sheet4.Cells(j, i + 1) = rs(i)
Next
j = j + 1
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing

End Sub

To test your data, I would start with this to eliminate the error:
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.
Jerry

Jerry

Thank you for your reply, i will try out your recomendations shortly

Update works brilliant, thanks

Mark