Avoid extra "carriage return" in Print statement with Visual Basic?

Use a trailing semicolon to surpress the new line:

Print #outFile, "#+TITLE:     Weekly Report";                                            ^                                            ^

The VB Editor will often add a semicolon if you make a mistake in the statement which could explain why the new line is sometimes output and sometimes not.

New diagnostic routine

We need to know the character within cell A1 that is causing the problem.

Place the following subroutine within one of your modules.

Public Sub DsplInHex(Stg As String)  Dim Pos As Long  For Pos = 1 To Len(Stg)    Debug.Print Hex(AscW(Mid(Stg, Pos, 1))) & " ";  Next  Debug.PrintEnd Sub

Go to VB Editor's Immediate window and type in the following text following by Return:

DsplInHex(Sheets("Sheet1").range("A1"))

Underneath this line, you should see something like 54 65 73 74 31. This is a list of the code value of each character in the cell. I expect we will see A, the code for line feed, or D, the code for carriage return, at the end of the list.

Position the cursor in cell A1. Click F2 to select edit then Backspace to delete the invisible trailing character then Return to end the edit. Go back to the Immediate Window, position the cursor to the end of DsplInHex(Sheets("Sheet1").range("A1")) and click Return. The trailing character should have gone.

Try that and report back. Good luck.

To help the other people in the future, here is an summary of my problem and the solution. The extra carriage return on each line even with semi-colon at the print statement end was actually caused by a string of space followed by newline (Chr$(A)) in one of the print statement, once such string is printed, then all previous and subsequent printed content would have an extra carriage return!

It seems a bug on VBA 6 (with Excel 2007), a nasty one!

My work-around was to replace the newline by a space.

Thanks for Tony's repeated help enabling me finally nailed down the cause.

Here is the code to demonstrate the problem:

Sub DemoCarriageReturnWillAppearOnAllLines()    Dim filePath As String    Dim outFile    Dim offendingText    filePath = "d:\tmp\demoCarriageReturn.org"    If Dir(filePath) <> "" Then        Kill filePath    End If    outFile = FreeFile()    Open filePath For Output As outFile    Print #outFile, "#+AUTHOR:    Yu Shen" & vbNewLine;    Close #outFile 'At this moment, there is no carriage return    Open filePath For Append As outFile    offendingText = " " & Chr$(10)    Print #outFile, offendingText & vbNewLine;    Close #outFile 'Now, every line end has carriage return.    'It must be caused by the offending at the above print out content.End Sub

After the first "Close #outFile", here is the content of the file demoCarriageReturn.org:

#+AUTHOR:    Yu Shen

Note: with editor capable showing carriage return as visible ^M, there is no carriage return present.

However, after the second "Close #outFile", here is the content of the same file with additional content:

#+AUTHOR:    Yu Shen^M^M 

Note: there are two carriage returns appear. They are not intended. Especially, to the first line, the print statement has been executed, and at the previous close statement, it was found without carriage return. (To illustrate carriage return, I have to typing ^M in web page here. But it's in the file of the print out.)

This is why I think that it's a bug, as the carriage returns are not intended. It's undesirable surprise.

The following code shows that if I filter out the linefeed character the problem would be gone.

Sub DemoCarriageReturnWillNotAppearAtAll()    Dim filePath As String    Dim outFile    Dim offendingText    filePath = "d:\tmp\demoCarriageReturn.org"    If Dir(filePath) <> "" Then        Kill filePath    End If    outFile = FreeFile()    Open filePath For Output As outFile    Print #outFile, "#+AUTHOR:    Yu Shen" & vbNewLine;    Close #outFile 'At this moment, there is no carriage return    Open filePath For Append As outFile    offendingText = " " & Chr$(10)    Print #outFile, Replace(offendingText, Chr$(10), "") & vbNewLine;    Close #outFile 'Now, no more carriage return.    'The only change is removing the linefeed character in the second print statementEnd Sub

After full execution of the above program, there is indeed no carriage return!

#+AUTHOR:    Yu Shen

This shows that string combination of space followed by linefeed caused the bug, and removing linefeed can avoid the bug.

The following code further demonstrate that if there is no offending string, even without newline and semi-colon at the end of print statement, there would not be undesired carriage return!

Sub DemoCarriageReturnWillNotAppearAtAllEvenWithoutNewLineFollowedBySemiColon()    Dim filePath As String    Dim outFile    Dim offendingText    filePath = "d:\tmp\demoCarriageReturn.org"    If Dir(filePath) <> "" Then        Kill filePath    End If    outFile = FreeFile()    Open filePath For Output As outFile    Print #outFile, "#+AUTHOR:    Yu Shen"    Close #outFile 'At this moment, there is no carriage return    Open filePath For Append As outFile    offendingText = " " & Chr$(10)    Print #outFile, Replace(offendingText, Chr$(10), "")    Close #outFile 'Now, no more carriage return.    'The real change is removing the linefeed character in the second print statementEnd Sub

Also in the output result:

#+AUTHOR:    Yu Shen

Still no the annoying carriage return!

This shows that using newline followed by semi-colon at the end of print statement is not the solution to the problem of carriage return at every line! The real solution is to avoid any string of space followed by linefeed in the print out content.

Yu