Carriage Return/Line Feed control codes in Excel

[SteveShanks]

Hi Everyone

How can I stop Excel replacing a CR/LF in a text string with a 'square' character code when I programtically insert text into a cell in EXCEL?

I'm using VB.NET with Excel 2003

Many Thanks
Steve

 

[udo321]

Have you tried just using a vbLf?

 

[SteveShanks]

The problem is that the string is coming from an Access 2003 database text field that was populated from a VB.NET textbox control so I don't have any control over the control codes for new lines.

If I build a string manually in VB using vbCrLf etc it appears without the control codes in Excel....it's just the strings originating from multiline VB text boxes that cause the problems.

Thanks
Steve

 

[udo321]

Are you able to do a replace on the string before you assign it to a cell?
myString = myString.Replace(vbCrLf, vbLf)
or
myString = myString.Replace(Chr(13), "")

 

[SteveShanks]

Thanks udo321....spot on!

I used
excel.cells(1,1) = mystring.Replace(ControlChars.CrLf, ControlChars.Lf)

Thanks again
Steve