Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005
    Provided Answers: 1

    Question 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

    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)
    j = j + 1

    Set rs = Nothing

    Set conn = Nothing

    End Sub

  2. #2
    Join Date
    Jan 2002
    Bay Area
    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.

  3. #3
    Join Date
    May 2005
    Provided Answers: 1

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

    Update works brilliant, thanks

    Last edited by MarkWhyte; 12-14-11 at 10:49.

Posting Permissions

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