suran78
asked on
Line feed within cell/ formatting within cell in Excel
Experts,
I have a search engine and it gives results in HTML. The results has to be dumped in EXcel sheet according to a certain format.
I have a small trial program that can be executed. This code prints data on seperate cells. How can I give line feed inside one single cell ? Please help me with the code and modifiy it so that I can get Line2 below line1 in a single cell. If this is not the right place to ask this question, please direct me to the right location.
Here is the code:
<%
Response.Expires = -1000
Const cDocFolderRoot = "virtual"
Const cDocFolderPath = "\data\doctemp\"
Dim oXLS, mXLSFile, mPathInternal, mPathVirtual
mXLSFile = getFileName(Session("UserI D"))
Const cDocFolder = "\data\doctemp"
Const cXLSext = ".xls"
Dim thePath, theFile
mPathInternal = Server.MapPath("/" & cDocFolderRoot) & cDocFolderPath
mPathVirtual = cDocFolderRoot & Replace(cDocFolderPath, "\", "/")
' Use Office Web Components
Set oXLS = Server.CreateObject("OWC.S preadsheet ")
'!!! problem: Chr(10) or Chr(13) causing data to be written to the next cell down
oXLS.Cells(1, 1).Value = "Line1" & Chr(10) & "Line2"
'!!! problem: file not written if already exists
oXLS.ActiveSheet.Export mPathInternal & mXLSFile, 0
'!!! doesn't work - client not able to access file from internal map
' Response.Redirect mPathInternal & mXLSFile
Set oXLS = Nothing
Function getFileName(ByVal pDowID)
'
' return an Excel file name
'
getFileName = "ProdMix_" & getDateStamp(Date) & "_" & pDowID & cXLSext
End Function
Function getDateStamp(ByVal pDate)
'
' return date string in the form yyyy-mmm-dd
'
Dim yy, mm, dd
yy = Year(pDate)
mm = MonthName(Month(pDate), True)
dd = Day(pDate)
If dd < 10 Then dd = "0" & dd
getDateStamp = yy & "-" & mm & "-" & dd
End Function
%>
<html>
<body>
<h2>Excel Test</h2>
<p><a href="<%=mPathVirtual & mXLSFile %>">click here for spreadsheet</a></p>
<p>
<br>
<b>Debug:</b>
<br>
<br>
mPathInternal = <%=mPathInternal %><br>
mPathVirtual = <%=mPathVirtual %><br>
mXLSFile = <%=mXLSFile %><br>
</p>
<p>Refreshed: <%=Date() & " " & Time() %></p>
</body>
</html>
I have a search engine and it gives results in HTML. The results has to be dumped in EXcel sheet according to a certain format.
I have a small trial program that can be executed. This code prints data on seperate cells. How can I give line feed inside one single cell ? Please help me with the code and modifiy it so that I can get Line2 below line1 in a single cell. If this is not the right place to ask this question, please direct me to the right location.
Here is the code:
<%
Response.Expires = -1000
Const cDocFolderRoot = "virtual"
Const cDocFolderPath = "\data\doctemp\"
Dim oXLS, mXLSFile, mPathInternal, mPathVirtual
mXLSFile = getFileName(Session("UserI
Const cDocFolder = "\data\doctemp"
Const cXLSext = ".xls"
Dim thePath, theFile
mPathInternal = Server.MapPath("/" & cDocFolderRoot) & cDocFolderPath
mPathVirtual = cDocFolderRoot & Replace(cDocFolderPath, "\", "/")
' Use Office Web Components
Set oXLS = Server.CreateObject("OWC.S
'!!! problem: Chr(10) or Chr(13) causing data to be written to the next cell down
oXLS.Cells(1, 1).Value = "Line1" & Chr(10) & "Line2"
'!!! problem: file not written if already exists
oXLS.ActiveSheet.Export mPathInternal & mXLSFile, 0
'!!! doesn't work - client not able to access file from internal map
' Response.Redirect mPathInternal & mXLSFile
Set oXLS = Nothing
Function getFileName(ByVal pDowID)
'
' return an Excel file name
'
getFileName = "ProdMix_" & getDateStamp(Date) & "_" & pDowID & cXLSext
End Function
Function getDateStamp(ByVal pDate)
'
' return date string in the form yyyy-mmm-dd
'
Dim yy, mm, dd
yy = Year(pDate)
mm = MonthName(Month(pDate), True)
dd = Day(pDate)
If dd < 10 Then dd = "0" & dd
getDateStamp = yy & "-" & mm & "-" & dd
End Function
%>
<html>
<body>
<h2>Excel Test</h2>
<p><a href="<%=mPathVirtual & mXLSFile %>">click here for spreadsheet</a></p>
<p>
<br>
<b>Debug:</b>
<br>
<br>
mPathInternal = <%=mPathInternal %><br>
mPathVirtual = <%=mPathVirtual %><br>
mXLSFile = <%=mXLSFile %><br>
</p>
<p>Refreshed: <%=Date() & " " & Time() %></p>
</body>
</html>
I should've been more clear there regarding the line feed issue. I personally don't know of a way to tell excel to use a line feed via character code or using a VB Carriage Return (vbCr). I do know that if you make an HTML table and have line breaks within your td tags, they will be treated as carriage returns in the exported excel file.
ASKER
I have done this already. The problem is I have to format lines in single cell.
So, it should look like this in single cell:
------------------
Line1 <br>
Line2
------------------
With Html fomating I am getting
first cell:
line1
--------------------
next cell
line2
suran
So, it should look like this in single cell:
------------------
Line1 <br>
Line2
------------------
With Html fomating I am getting
first cell:
line1
--------------------
next cell
line2
suran
Alas, you are correct. My memory failed me. If you put a border on your table, you can see that the cell borders will be in the right place, but the items still show up in different cells.
In excel VBA we can enter line feed as
Range("A1").Select
ActiveCell.FormulaR1C1 = "this is a test" & Chr(10) & "new line"
You need to activate the cell before inserting the line feed char (10).
In the same way ... you can try...
oXLS.Range("A1").Select
ActiveCell.FormulaR1C1 = "Line1" & Chr(10) & "Line2"
This is just a theory...good luck!
Range("A1").Select
ActiveCell.FormulaR1C1 = "this is a test" & Chr(10) & "new line"
You need to activate the cell before inserting the line feed char (10).
In the same way ... you can try...
oXLS.Range("A1").Select
ActiveCell.FormulaR1C1 = "Line1" & Chr(10) & "Line2"
This is just a theory...good luck!
ASKER
Shailesh,
Thanks fo rreplying. I tried your code, the FormulaR1C1 is giving error.
Thanks fo rreplying. I tried your code, the FormulaR1C1 is giving error.
did you try value property?..
ActiveCell.Value = "this is a test" & Chr(10) & "new line"
I was expecting error while range selection!
ActiveCell.Value = "this is a test" & Chr(10) & "new line"
I was expecting error while range selection!
ASKER
I am not getting any error by changing formulaR1C1 to value. But teh spreadsheet is not opening and I am not getting any result.
suran
suran
ASKER
I tried
ActiveCell.Value = "this is a test" & Chr(10) & "new line"
The "new line" is still coming on next cell but not inside the single cell with "this is a test"
ActiveCell.Value = "this is a test" & Chr(10) & "new line"
The "new line" is still coming on next cell but not inside the single cell with "this is a test"
ASKER
I have got the answer, please check this out, if you are eager to know teh solution:
https://www.experts-exchange.com/questions/20959144/Line-feed-within-cell-formatting-within-cell-in-Excel.html
Thanks for replying and trying.
suran
https://www.experts-exchange.com/questions/20959144/Line-feed-within-cell-formatting-within-cell-in-Excel.html
Thanks for replying and trying.
suran
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<%
If Request.QueryString("displ
Response.Expires = 0
Response.Buffer = True
Response.Clear
Response.ContentType = "application/vnd.ms-excel"
End If
%>
<html>
<head>
<title>Your page title</title>
</head>
<body>
<% If Not Request.QueryString("displ
<a href="pagename.asp?display
<% End If %>
'build your table of search results here
</body>
</html>