# Solved: VBA - Access to Excel 255 char limit issue



## Center (Jan 13, 2009)

So I have this Access program that takes values from a table in Access and writes reports to Excel via VBA. One of the fields being used is a Memo field and does contain text over 255 characters long.

I've searched Google and the forums here, but is there a way to insert a string (over 255 characters long) into a single cell in Excel without resorting to cell merging? Like I saw someone suggest somewhere that if a cell is 300 characters long, to split it up into 2 cells, then have Excel merge the two cells together somehow. But this isn't feasible for what I'm trying to do.

I've tried the suggestions found here:
http://support.microsoft.com/kb/105416
Although this is writing to a text box, I re-coded it to write to a cell (to like Sheets(1).Cells(1,1)), but it doesn't work. Does anyone have any suggestions? Or is this impossible to do under Office 2003?


----------



## slurpee55 (Oct 20, 2004)

As far as I can recall, you can enter more than 255 characters in Excel if you type directly into the formula box at the top of the spreadsheet. However, all the text will not be visible unless you click into the formula box and hit the End key on your keyboard nor will it be visible when you print unless you greatly expand the cell width and/or height.


----------



## Jimmy the Hand (Jul 28, 2006)

I agree with Loche, you can write strings longer than 255 characters into a cell.
I made a testing routine (see below) and determined that up to 1024 characters there's no problem with either passing a string from function to subroutine, or with putting that string into a cell. See it for yourself.


```
Sub enter_text()
    Dim ex As Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet, r As Excel.Range
    Dim i As Long, input_string As String
    
    Set ex = New Excel.Application
    ex.Visible = True
    Set wb = ex.Workbooks.Add
    Set ws = wb.Worksheets(1)
    For i = 1 To 1350
        Set r = ws.Range("A" & i)
        input_string = build_text(i)
        On Error GoTo err_handler
        r.Value = input_string
    Next
    Exit Sub
err_handler:
    MsgBox "Unable to insert string that is " & Len(input_string) & " characters long."
    End
End Sub
Function build_text(set_length As Long) As String
    Dim s As String, i As Long
    For i = 1 To set_length
        s = s & "+"
    Next
    build_text = s
End Function
```
Is 1024 chars enough?

Jimmy


----------



## Center (Jan 13, 2009)

hmm, thank you Jimmy. So I see that setting the cell's value to a string over 255 chars will work.

I should have specified in the OP, and it's my fault for not doing so, but is it possible to insert text at a particular location? So in the Access code I have this:

```
If Len(vRet) + xBeginsAt > 255 Then _
     vRet = Left(vRet, 255 - xBeginsAt)
ws.Cells(lRow, lCol).Characters(xBeginsAt, 0).Insert vRet
```
(I've inserted the If statement to deal with vRet strings over 255 chars) *vRet* is string that can be up to 500-600 characters (so yes, 1024 chars is plenty long enough). *xBeginsAt* is the location where the text is to be entered (xBeginsAt is normally 1, but can be in multiple locations). *ws* is the Excel.Worksheet that I'm writing to. *lRow, lCol* is the cell address where vRet is being inserted into.

I can probably modify this code to just use .Value = vRet if Len(vRet) > 255, but the preference (based on what I'm trying to do) is to insert text at location xBeginsAt.

Thanks again!


----------



## Jimmy the Hand (Jul 28, 2006)

Well, I would do the inserting this way:


```
Dim sTemp As String
    sTemp = Left(ws.Cells(lRow, lCol).Value, xBeginsAt)
    sTemp = sTemp & vRet
    sTemp = sTemp & Mid(ws.Cells(lRow, lCol).Value, xBeginsAt + 1)
    ws.Cells(lRow, lCol).Value = sTemp
```
The code below is the same, but in a shorter and more elegant form.

```
With ws.Cells(lRow, lCol)
        .Value = Left(.Value, xBeginsAt) & vRet & Mid(.Value, xBeginsAt + 1)
    End With
```
Jimmy


----------



## Center (Jan 13, 2009)

Think that will work. Thank you Jimmy!


----------



## slurpee55 (Oct 20, 2004)

Sweet work, Jimmy!


----------

