Can get carriage return to work

[jrice174]

I have an automated Word document that looks to an Excel file where I am saving lines of text. I use strVariable = ExcelSheet.Cells(i, 2) to get the text in the file but it doesn't see the carriage returns. I've tried vbCr and vbLf and Chr$(10) but nothing seems to work. I have

"this bit is text" & CHR$(13) & "this bit is more text"

but it doesn't create the line feeds for some reason. Any suggestions?

 

[TheTick]

Try chr(10) & chr(13) together

"Line 1" & chr(10) & chr(13) & "second line"

 

[jrice174]

It still sees it as one line

 

[jrice174]

I can get the results I want doing it this way, but I was hoping to put all the text in one Excel cell.
I the data in the first column matches strVaraible then get the data in second column.

Found = false
For i = 5 To 50
If Excelsheet.Cells(i, 1) = "" Then Exit For
If Excelsheet.Cells(i, 1) = strVariable Then
strResults = Excelsheet.Cells(i, 2).Value
If Found = True Then
strResults = strResults & Chr$(10) & Excelsheet.Cells(i, 2).Value
Else
strResults = strResults & Excelsheet.Cells(i, 2).Value
End If
Found = True
End If
Next i

 

[TheTick]

Make sure the cell is set to allow text wrap.

 

[jrice174]

Word wrap is on. It's in the Word document where I'm trying to get the line feeds to work.

 

[CajunCenturion]

Are you sure you copied the code correctly, because it looks like there is a logic problem in the posted code.

The boolean "found" can never be set to true because the assignment stated "found = true" is inside the conditional "If found = true".


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

I'm sorry, the indentation fooled me.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[IRstuff]

One cheap option might be to copy as cells into Word, and then convert table to text in Word.

TTFN

FAQ731-376

 

[joerd]

In Word, a new line is entered using [tt]Selection.TypeParagraph[/tt]. So you will have to split up the Excel cell value at vbLF (or chr(10), it's the same), and then use [tt]
Selection.TypeText Text:="first part"
Selection.TypeParagraph
Selection.TypeText Text:="second part"[/tt]

So your code would look something like this:

Dim a As String, parts() As String
Dim i As Integer
a = [a1].Value
parts = Split(a, vbLf)
For i = 0 To UBound(parts)
    Selection.TypeText Text:=parts(i)
    Selection.TypeParagraph
Next i

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[TCARPENTER1]

Hi jrice174,

How about vbCrLf?

 

[xwb]

In Word, the end of line is chr(13) & chr(7). It is 7: not 10. I don't know why it is 7 (which is a bell character in ASCII).

 

[jrice174]

That shows up in the text as
"this bit is text" vbCr "this bit is more text
and not breaking it down into separate lines>
Could it have something to do with the quotes?

 

[Denial]

XWB

Maybe it's the "ding" that the old manual typewriters used to make when you got near the end of the writing width. (Just before you slapped to platten back across to the left.)

 

[jrice174]

Good answer! I need this kind of input more often to help me get throught the day.

 

[TheTick]

One way to check would be to parse a string copied out of MS Word. Write a quickie program with a textbox. Copy-and-paste a mulitline snippet from MS Word into the textbox, then loop through all the characters and get the ASCII codes.

 

[jrice174]

That's what I've done, shown below

Public Sub ReplaceCarriageReturns()
Dim i As Integer
Dim L As Integer
Dim X As Integer
For i = 1 To 5
X = InStr(strATextValue, " & vbcr & ")
If X = 0 Then Exit Sub
If X > 0 Then
L = Len(strATextValue)
strATextValue = Left(strATextValue, X - 1) & vbCr & Right(strATextValue, L - (X + 9))
End If
Next i
End Sub

Thanks for the ideas

 

[joerd]

You wrote a nice replacement for the Replace method. The short version is:
[tt]strATextValue = Replace(strATextValue, vbLf, vbCr)[/tt]
Modify as needed, it's pretty flexible.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[jrice174]

Thank You! Thank You! Thank You!

That's a new command to me and it did exactly what i was looking for.