copy cells with more then 255 chars...

[Xavalon]

Hi All,

I hit the 255 char limit when coping cells (more than 255 chars are
truncated), but even after looking through tens of pages I couldn't
find any good answer. My hope it that someone here knows a good answer

BTW this is from my work computer, Excel 2000, but I had the same on a
Excel 2003 computer.

I have made a Excel VBA sub that merges 2 sheets in 2 books together
on a new sheet; However sheet A, containing some cells larger then 255
chars but has less rows than sheet B. Copying sheet A to C first is no
option.

my code simplified:

.....
Dim c as Range
Dim i as Interger, rw as Integer
set wsa workbook("filenameA.xls").worksheets("A")
set wsb workbook("filenameB.xls").worksheets("B")
set wsc workbook("filenameB.xls").worksheets("C")
.....
' I lookup certain values in sheet B and copying them to sheet C....
.....
' value to be looked up in sheet A is in variable i now
.......
' set range in where to find i
' rw contains correct row in worksheet c to write values
.....
Set c = .Find(i, LookIn:=xlValues)
If Not c Is Nothing Then

' trying to overcome with copying an paste special
wsa.Cells(c.row, 8).Copy
wsc.Cells(rw, 10).PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
' this apparently doesn't copy more then 255 chars

' found some code op MS website,
' to be honost I am not a regular VBA programmer, don't understand the
' Dim st2() as integer very well.
' Argh! is it not working too well, getting errors on cells with more
than
' 255 chars. Others are copied.

Dim st1 as string
Dim st2() as string
Dim x as integer

st1 = wsb.cells(c.row,2).value
For x = 1 To (Int(Len(st1) / 255) + 1)
st2(x) = Mid(st1, ((x - 1) * 255) + 1, 255)
Next x

wsc.cells(rw,11) = st2
' Argh! is it not working too well, getting errors on cells with more
than
' 255 chars. Others are copied.

end if

------------

 

[Berend Botje]

Try something like:

Cells1.value = Cells2.value

I think that works above 255 char as well....

By the way, there is a typo in your program:

Dim i as Interger , rw as Integer

Should be Integer

 

[Tom Ogilvy]

This article describes this as a limitation for copying the entire sheet

http://support.microsoft.com/default.aspx?scid=kb;en-us;213548&Product=xlw2K
XL2000: "255 Characters in Cell" Error When Copying Worksheet

and the solution is to copy cells - so I haven't heard of this being a
problem if copying a range (something less than the worksheet itself).

I did this in Excel 2000

Sub TestCopy()
Debug.Print Len(ActiveCell)
ActiveCell.Copy Destination:=ActiveSheet.Next.Range("B9")
Debug.Print Len(ActiveSheet.Next.Range("B9"))
End Sub

it produced:
678
678

so It had no problem doing the copy

This
Sub TestCopy()
Debug.Print Len(ActiveCell)
ActiveCell.Copy
ActiveSheet.Next.Range("B9").PasteSpecial xlValues
Debug.Print Len(ActiveSheet.Next.Range("B9"))
End Sub

produced as well
678
678

 

[Tom Ogilvy]

This worked as well to match your parameters exactly:

Sub TestCopy()
Debug.Print Len(ActiveCell)
ActiveCell.Copy
ActiveSheet.Next.Range("B9").PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Debug.Print Len(ActiveSheet.Next.Range("B9"))
End Sub


Produced
678
678

 

[xav alon]

Thanks guys, but I am not there yet.
But it looks like I hit a bug or is a MS feature ;-)

wksa.cells(row, 12).copy
Cells(row, 10).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

works, but where wksb is referenced to a worksheet it doesn't.

wksa.cells(row, 12).copy
wksb.Cells(row, 10).PasteSpecial Paste:=xlValues, _ Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

it truncates to 255 chars again. When I msgbox wksa.cells(row,12) I get
the long text!

 

[Tom Ogilvy]

I was working with two different sheets and didn't have the problem.

 

[Michelle]

How can you get more than 255 characters in the footer?

 

[Guest]

I have worse case. I have to put in my programm value to cell that exids 255 characters. It cuts it. I have no Cell with this value I should write somthing like

string val = " ..."; //more than 255 simbols
((Excel.Range)sheet.Cells[i, j]).Value2 = val; (C#)
WHAT SHOULD I DO ?