Sending text to Excel cell - Carriage returns disappear!

[bentheimmigrant]

I'm trying to send strings from Access vba to excel. Some of the strings have multiple lines. I've tried using vbCr, vbCrLf, and Chr(13) & Chr(10), but all of these end up with nothing between them in Excel, all on a single line.

To clarify, I'm building the string, and then sending it to the cell. I am not (and would reaaaalllllly like to avoid) dynamically writing to the cell.

I am using late binding, but I don't see how that would make a difference.

So essentially:

xlRange = "Some text" & vbCr & "Some more text"

'AND
xlRange = "Some text" & vbCrLf & "Some more text"

'AND
xlRange = "Some text" & Chr(13) & Chr(10) & "Some more text"

all put "Some textSome more text" into the cell xlRange.

Halp?

 

[static]

Works ok here

With CreateObject("excel.application")
    .Visible = True
    With .Workbooks.Add.ActiveSheet.Range("a1")
        .Formula = "foo" & vbNewLine & "bar" & vbNewLine & vbNewLine & "foo" & vbNewLine & "bar"
    End With
End With

 

[bentheimmigrant]

Sorry.

I was trying to be helpful, and quickly wrote some example code, doing exactly what I'm trying not to do. I simplified it so much I got rid of the string. Too much happening.

Let's try:

Str = "Some text" & vbCr & "Some more text"
xlRange = Str

'AND

Str = "Some text" & vbCrLf & "Some more text"
xlRange = Str

'AND

Str = "Some text" & Chr(13) & Chr(10) & "Some more text"
xlRange = Str

THAT is much more what I'm doing, and it's returning concatenated strings in excel.

 

[static]

How is that different to what you wrote before?

The important part that you keep missing out is, what is xlRange?

edit

You might want to check that wrap text is turned on for the cells.
format cells > alignment > wrap text

 

[bentheimmigrant]

It's just a cell (which I tried to imply in my first post).

The reason it's different from what I wrote before is I don't know. But it doesn't work if the carriage return is in the vba string.

 

[static]

see my edit
wrap text should be on

 

[bentheimmigrant]

Wrap text is on, and it doesn't make a difference if it's off. Wrap text will only line break text that reaches the end of the cell, it doesn't have any effect on internal line breaks (using alt+enter or any of the vba methods).

In the formula window, the text is clearly shown as concatenated, so line breaks are essentially turning into null strings.

 

[bentheimmigrant]

Right. So although I had tested Chr(13) & Chr(10), I apparently didn't spot that it actually worked.

This is not my day. Apologies for wasting your time.

 

[Frothingslosh]

It happens.

 

[bentheimmigrant]

Indeed it does! I'm not ashamed (ok, well maybe a little) to own up to it.