Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cells with more then 255 chars...
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 ------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cells with more then 255 chars...
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cells with more then 255 chars...
This article describes this as a limitation for copying the entire sheet
http://support.microsoft.com/default...&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 -- Regards, Tom Ogilvy "Xavalon" wrote in message om... 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 ------------ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cells with more then 255 chars...
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 -- Regards, Tom Ogilvy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cells with more then 255 chars...
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! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cells with more then 255 chars...
I was working with two different sheets and didn't have the problem.
-- Regards, Tom Ogilvy "xav alon" wrote in message ... 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! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cells with more then 255 chars...
How can you get more than 255 characters in the footer?
-----Original Message----- 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 -- Regards, Tom Ogilvy . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy cells with more then 255 chars...
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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to truncate list of meaningful words greater than 15 chars tomeaningful words of 8 chars. | Excel Worksheet Functions | |||
Export from excel with cells 256 chars in | Excel Discussion (Misc queries) | |||
sum first 2 chars if 3rd is something... | Excel Discussion (Misc queries) | |||
Copy sheets with more than 255 chars in a cell? | Excel Worksheet Functions | |||
geomean ignoring blank cells and chars | Excel Worksheet Functions |