255 Character Cell Limit From VBA Workaround?

[eddiev1972]

I am trying to pass a string from VBA excel to a cell on a worksheet and am encountering the 255 character limit that is truncating my string. I have found a workaround for some objects at Microsoft, but It does not seem to work on cells.

I was hoping someone could assist me with any suggestions.

When I try the below, string NoteString1 correctly appears in the cell, but NoteString2 does not appear at all.

Below is my current code

   NoteString1 = "Note 1 Here"  'actual note is over 200 characters
   NoteString2 = "Note 2 Here"  'actual note is over 100 characters
                    
   FirstNote = Len(NoteString1) + 1
                    
   Cells((i * Spacer + 6), 4).Characters(Start:=1).Text = NoteString1
   Cells((i * Spacer + 6), 4).Characters(Start:=FirstNote).Text= NoteString2

Thank you for any assistance.

 

[prabby25101981]

Try putting them in two different cells then concatenating both in the original cell. That should do...

 

[Richard Schollar]

Hi

There's no 255 character limit that i know of - are you doing this wholly within Excel VBA or are you automating thru another Office application (eg Word)?

 

[pgc01]

Hi

The 255 character limit you are encountering is because you are using the Characters property of the Range object, which you do not need to do. Use the Value property to assign the string

Dim s as String
 
s = "String with more than 255 characters"
Cells(1, 1).Value = s

 

[AlphaFrog]

   NoteString1 = String(250, "A") 'Example text: 250 characters
   NoteString2 = String(500, "Z") 'Example text: 500 characters
                    
   Cells((i * spacer + 6), 4).Formula = NoteString1
   Cells((i * spacer + 6), 4).Formula = Cells((i * spacer + 6), 4).Formula & NoteString2
   
   MsgBox "The length of text in Cell " & Cells((i * spacer + 6), 4).Address(0, 0) & " is " & Len(Cells((i * spacer + 6), 4))

 

[eddiev1972]

Thank you for all the response. I am using Excel 2003 (I am not sure if that matters) and generating my string from within VBA.

I still have a problem, but it is appears related to the fact that I am taking my sheet and copying it to a new new workbook. I was only viewing the final report, as the interim report was deleted in the process.

Thanks to the Message Box included with Alpha Frog's solution I see the interim report did have cells with more than 255 characters. However, when copying and pasting this particular sheet to a new workbook, any cells that have 255+ characters ar getting truncated to 256.

So, this appear to be a bit of a different problem.

I will play with it a bit, but if anybody has any immediately solutions, it would be greatly appreciated.

Thanks again for the great community response.

 

[AlphaFrog]

How are you copying and pasting? The method you use may be the issue.

 

[rizvisa1]

You have the string in the cell. What you are seeing is the limitation of excel in displaying all the characters. if you try to print the content of the cell or even check the length you would see that it shows that string is of correct length. There is no workabout to have excel cell show all characters.

 

[eddiev1972]

Below is the code I am using the creates the final report in its own workbook.

Sheets("PAM") is the intermin report format that is in the same file as the primary data. "PAM" is cleared out after the the process is completed. If I don't clear it out, I see that the Strings are not truncated on the intermin report.

They are only truncated on the newly created workbook, that, in all other respects, is the same as "PAM"

Sheets("PAM").Select
Sheets("PAM").Copy
Application.DisplayAlerts = False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWindow.Close
MsgBox "Report saved to " & FileSaveName

 

[Andrew Poulsom]

Look here:

http://support.microsoft.com/kb/213548