Avoid the extra "carriage return" in a Print statement using Visual Basic?

Question

With Visual Basic, I am confused by the fact that the behavior of the Print statement is that sometimes the following statement: will return an extra carriage "^ M" at the end of the line, but sometimes it is not. I wonder why?

filePath = "d:\tmp\FAE-IMM-Report-2012-Week.org" If Dir(filePath) <> "" Then Kill filePath End If outFile = FreeFile() Open filePath For Output As outFile Print #outFile, "#+TITLE: Weekly Report" 

will create

 #+TITLE: Weekly Report^M 

while I wish without ^ M:

 #+TITLE: Weekly Report 

In one test program, almost the same code will not produce “^ M”.

Please, help! Many thanks.

After further experiment, I found that the following sentence using vbNewline and ";" at the end of print content, still does not solve my problem.

After careful isolation, I found that the cause of the problem is a character that seems to be a space, not just a space, followed by a new line and a carriage return. Before printing the text containing the offending line, carriage return is not possible, but as soon as the violation line is printed, each line, including the previous line, will have a carriage return.

I am not sure which particular offensive line is, since my VBA skill is still not too good.

Here is a copy of the offensive text from a spreadsheet cell:

  "There is something invisible after this visible text After the invisible text, then there might be a carriage return $Chr(13) and/or newline" 

I'm not sure if pastes will save content in a web browser. When pasting into emacs, I did not see a carriage return, while emacs should display it if there is one. Therefore, I assume that there is no carriage return in the violating line.

The following is a program that demonstrates the problem:

  Sub DemoCarriageReturnWillAppear() 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 = ThisWorkbook.Worksheets("Sheet1").Range("A1") Print #outFile, offendingText & vbNewLine; Close #outFile 'Now, every line end has carriage return. 'It must be caused by something offending at the above print out content. End Sub 

Here is the final result of the procedure described above:

  #+AUTHOR: Yu Shen^M There is something invisible after this visible text After the invisible text, then there might be a carriage return $Chr(13) or newline^M 

Please note that the above “^ M” is added by me, as carriage returns will not be displayed in the browser.

If you're interested, I can send you an excel file with offensive content.

I need your help on how to avoid this breaking line, or carriage return. (I’m even trying to make a line Replace carriage return or a new line, because I found that as soon as I manually delete all the changes caused by another line, the problem will disappear. But the Replace call to replace vbNewline, Chr $ (13) or vbCrLf has no values.

Thanks for your further help!

YU

Answer 1

To help other people in the future, here is a brief overview of my problem and solution. The extra carriage return on each line, even with a semicolon at the end of the print report, was actually caused by a space line, followed by a new line (Chr $ (A)) in one of the print statements, after such a line has been printed, and then all previous and subsequent print content will have an extra carriage return!

It seems the error in VBA 6 (since Excel 2007) is nasty!

My job was to replace a new line with a space.

Thank you for Tony repeating the help that allowed me to finally nail the cause.

Here is the code demonstrating 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 contents of the demoCarriageReturn.org file:

 #+AUTHOR: Yu Shen 

Note. An editor capable of displaying carriage returns as visible ^ M, there is no carriage return.

However, after the second "Close #outFile", it contains the contents of the same file with additional content:

 #+AUTHOR: Yu Shen^M ^M 

Note. Two carriage returns will appear. They are not intended. Moreover, in the first line the print statement was executed, and in the previous closed statement it was found without carriage return. (To illustrate the carriage return, I have to type ^ M on the web page here, but this is in the printout file.)

This is why I think this is a mistake since carriage return is not intended. This is an unwanted surprise.

The following code shows that if I filter out the line feed character, the problem will disappear.

 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 statement End Sub 

After the complete execution of the above program, there really is no carriage return!

 #+AUTHOR: Yu Shen 

This shows that the string combination of the space followed by the string caused an error, and deleting the newline may avoid the error.

The following code further demonstrates that if there is no line with an invalid line at the end of the print instruction, even without a newline and half-column, there would be no unwanted 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 statement End Sub 

Also in the output:

 #+AUTHOR: Yu Shen 

There is still no annoying carriage!

This shows that using a new line followed by a semicolon at the end of the print statement is not a solution to the carriage return problem on every line! . The real solution is to avoid any line of space followed by the output of the line in the printed content.

YU

Answer 2

Use a semicolon to stop a new line:

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

The VB editor often adds a semicolon if you are mistaken in a statement that may explain why sometimes a new line is output and sometimes not.

New diagnostic procedure

We need to know the character in cell A1 that causes the problem.

Put the following routine in 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.Print End Sub 

Go to the VB Editor Immediate window and enter the following text following Return :

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

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

Place the cursor in cell A1. Press F2 to select edit, then Backspace to remove the invisible trailing character, and then Return to finish editing. Return to the Immediate window, place the cursor at the end of DsplInHex(Sheets("Sheet1").range("A1")) and press Return . The end character should disappear.

Try it and report back. Good luck.