×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Can get carriage return to work

Can get carriage return to work

Can get carriage return to work

(OP)
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?

RE: Can get carriage return to work

Try chr(10) & chr(13) together

CODE

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

RE: Can get carriage return to work

(OP)
It still sees it as one line

RE: Can get carriage return to work

(OP)
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

RE: Can get carriage return to work

Make sure the cell is set to allow text wrap.

RE: Can get carriage return to work

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

RE: Can get carriage return to work

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

RE: Can get carriage return to work

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

RE: Can get carriage return to work

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


So your code would look something like this:

CODE

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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Can get carriage return to work

Hi jrice174,

How about vbCrLf?

RE: Can get carriage return to work

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).
 

RE: Can get carriage return to work

(OP)
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?

RE: Can get carriage return to work

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.)

RE: Can get carriage return to work

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

RE: Can get carriage return to work

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.

RE: Can get carriage return to work

(OP)
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

RE: Can get carriage return to work

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

Cheers,
Joerd

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

RE: Can get carriage return to work

(OP)
Thank You! Thank You! Thank You!

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources