0

I'm exporting data into Excel file by generating a plain html structure in VBscript, like this:

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=excelTime.xls"
response.write "<table>"
response.write "<tr>"
response.write "<td>User Name</td>"
...

Some data cells contain multiple values, separated by comma. I have a requirement to write such values in a separate line each but within the same cell, without commas, like this: enter image description here

Just as if I have put Alt+Enter in Excel after each value. I try to use VBscript Replace() function to substitute commas with line breaks, like this:

Response.write "<td>" & Replace(Value, ",", " <br/> ") & "</td>"

But it breaks a cell into multiple cells instead:

enter image description here

How can I achive the desired result here? I tried different variants, but nothing helped:

Replace(Value, ",", " vbCrLf ") 'does not put a line break
Replace(Value, ",", " CHAR(13) ") 'puts the "CHAR(13)" text between values
Replace(Value, ",", " \\n ") 'puts the "\\n" text between values
Replace(Value, ",", " \n ") 'puts the "\n" text between values
Replace(Value, ",", " \r ") 'puts the "\n" text between values

Maybe you know some other way to do it?

user692942
  • 16,398
  • 7
  • 76
  • 175
BohdanZPM
  • 695
  • 2
  • 10
  • 22
  • W?ith your `Replace()` examples if you want to replace with `Chr(13)`, `vbCrLf` etc you have to pass the actual value not a string representation of that value - i.e `Replace(Value, ",", vbCrLf)`. – user692942 Jan 23 '17 at 11:25

1 Answers1

-1

Already figured this out. Instead of "<br/>" you should just use:

Response.write "<td>" & Replace(Value, ",", " <br style='mso-data-placement:same-cell;' /> ") & "</td>"
BohdanZPM
  • 695
  • 2
  • 10
  • 22
  • Already answered - [A: Classic ASP XLS output with carriage return in cell](http://stackoverflow.com/a/9158446/692942) – user692942 Jan 23 '17 at 11:50