Programming Line Breaks into Cells

[TheVisionThing]

I'm writing resumes from a database field into individual single cells in a
spreadsheet (one resume per cell). The problem is that line breaks in the
resumes are not being carried over into the single cells.

This is the function I'm using to correct this problem, but it's not
working.
-----------------------

Function ConvertTextAreaExcel(strText)
'inserts line breaks when displaying textarea data in an Excel cell
If strText <> "" then
ConvertTextAreaExcel = Replace(strText, chr(13),vbCRLF)
End If
End Function

 

[R.VENKATARAMAN]

did you try something like this
range("a1")="raja" & chr(10) &"raman"


chr(10) is equivalent ot alt+ener
and did you get what you want.

 

[TheVisionThing]

Didn't seem to work.

If I use 'ConvertTextAreaExcel = Replace(strText, chr(13)."#LINE#")', I see
'#LINE#' where the lines should be in the resume in the Excel cell.

But ' ConvertTextAreaExcel = Replace(strText, chr(13),chr(10))' doesn't
introduce line breaks into the cell.

Regards,
Wayne C.

 

[David McRitchie]

Did you turn on Cell wrap Format, cells, alignment, wrap
Alt+Enter turns on wrap for you.

Perhaps you have a different character

Determine if cell is Number or Text and why is it seen that way (#debugformat)
http://www.mvps.org/dmcritchie/excel/join.htm#debugformat

 

[TheVisionThing]

I've tried everything referenced, but nothing works.

If I look at the cell using the cell view utility, I don't see a chr(13).
If I replace chr(13) with "#CR#" I see it in the cell. If I replace chr(13)
with chr(10), I don't get to see line breaks in the cell. I've also tried
vbCRLF instead of chr(10).

 

[Dave Peterson]

Do you see a box where that vblf/chr(10) would be?

If yes, then you still haven't changed that cell to wordwrap:
format|cells|alignment tab|wrap text.

If you don't see a box for that vblf, have you increased the rowheight?

 

[TheVisionThing]

I found the solution!

I was using the following header in an ASP page to generate an Excel
spreadsheet from a database report.

----
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "content-disposition", "attachment;
filename=JPIJobsProfiles.XLS"
----

But the output streamed into excel did not contain any chr(10) line breaks -
hence no line breaks in my Excel cell.

The solution is two-fold.

1) At the server level use 'Replace(strText, chr(13), "<br />")' to convert
chr(10) into "<br />".
2) The problem then is that "<br />" in Excel would split the text over
multiple cells. The solution was to use the following style in the ASP
page for the '<br>' tag:

----
br {mso-data-placement:same-cell;}
----

I found this solution online, but am reposting for the benefit of others.

Regards,
Wayne C.

 

[David McRitchie]

Thanks Wayne, I would never have guessed that but then I don't like .asp pages to begin with. But I am beginning to use a few
more styles now. Good that you have control over both sides of your input.