vba 使用 Visual Basic 在 Print 语句中避免额外的“回车”？

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

提问by Yu Shen

With Visual Basic, I'm confused with that the behavior of Print statement in that sometimes the following statement: would cause additional carriage return "^M" at the end of a line, but sometimes, it doesn't. I wondering why?

对于 Visual Basic，我对 Print 语句的行为感到困惑，因为有时以下语句：会在行尾导致额外的回车“^M”，但有时不会。我想知道为什么？

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"

would produce

会产生

#+TITLE:     Weekly Report^M

while I wish without ^M:

虽然我希望没有 ^M：

#+TITLE:     Weekly Report

In one test program, almost the same code would produce no "^M".

在一个测试程序中，几乎相同的代码不会产生“^M”。

Please help! Thanks a lot.

请帮忙！非常感谢。

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

经过进一步的实验，我发现以下建议使用 vbNewline 和“;” 在打印内容结束时，仍然没有解决我的问题。

After careful isolation, I found the cause of the problem is an character that seems like a space, not exactly space, followed by newline and carriage return. Before printing the text containing the offending string, there was no carriage return, but once the offending line is printed, then every line including the previous line printed would have carriage return.

经过仔细的隔离，我发现问题的原因是一个看起来像空格的字符，而不是空格，后面跟着换行符和回车符。在打印包含有问题的字符串的文本之前，没有回车，但是一旦打印了有问题的行，那么包括前一行打印的每一行都会有回车。

I'm not sure what the exact the offending string is as my skill of VBA is not yet too well.

我不确定有问题的字符串到底是什么，因为我的 VBA 技能还不太好。

Here is a copy of the offending 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 the paste to web browser would preserve the content, though. By pasting to emacs, I did not see carriage return, while emacs should display it, if there is one. So I guess that there is no carriage return in the offending string.

不过，我不确定粘贴到 Web 浏览器是否会保留内容。通过粘贴到 emacs，我没有看到回车，而 emacs 应该显示它，如果有的话。所以我猜有问题的字符串中没有回车。

Below is the program demonstrate 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 above procedure:

以下是上述过程的最终结果：

    #+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

Note the above "^M" is added by me, as carriage return would not be visible in browser.

请注意，上面的“^M”是我添加的，因为在浏览器中回车是不可见的。

If you're interested, I can send you the excel file with the offending content.

如果您有兴趣，我可以将包含违规内容的 excel 文件发送给您。

I need your help on how to avoid those offending string, or the carriage returns. (I even try to do string Replace of the carriage return or new line, as I found that once I manually deleted whatever caused change to another line, the problem would be gone. But calling Replace to replace vbNewline, Chr$(13), or vbCrLf did not make any difference.

我需要你的帮助来避免那些有问题的字符串或回车。（我什至尝试对回车或新行进行字符串替换，因为我发现一旦我手动删除了导致更改另一行的任何内容，问题就会消失。但是调用 Replace 来替换 vbNewline, Chr$(13),或 vbCrLf 没有任何区别。

Thanks for your further help!

感谢您的进一步帮助！

Yu

于

采纳答案by Yu Shen

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!

为了将来帮助其他人，这里是我的问题和解决方案的摘要。即使在打印语句末尾有分号，每一行的额外回车实际上是由一个打印语句中的一串空格后跟换行符 (Chr$(A)) 引起的，一旦打印了这样的字符串，那么所有之前和之后的打印内容会有额外的回车！

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

这似乎是 VBA 6（使用 Excel 2007）上的一个错误，一个令人讨厌的错误！

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:

在第一个“关闭#outFile”之后，这里是文件 demoCarriageReturn.org 的内容：

#+AUTHOR:    Yu Shen

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

注意：编辑器能够将回车显示为可见的 ^M，不存在回车。

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

但是，在第二个“关闭#outFile”之后，这里是具有附加内容的同一个文件的内容：

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

注意：有两个回车出现。他们不是故意的。特别是到了第一行，print语句已经执行完毕，在之前的close语句中，发现没有回车。（为了说明回车，我必须在此处的网页中键入 ^M。但它在打印输出的文件中。）

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

这说明空格后跟换行符的字符串组合导致了这个bug，去掉换行符可以避免这个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!

下面的代码进一步说明，如果没有违规字符串，即使在print语句末尾没有换行和分号，也不会有不想要的回车！

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

这说明在print语句末尾使用换行符后加分号并不能解决每一行回车的问题！真正的解决方案是在打印输出内容中避免任何空格后跟换行符。

Yu

于

回答by Tony Dallimore

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.

如果您在语句中犯了错误，VB 编辑器通常会添加一个分号，这可以解释为什么有时输出新行有时不输出。

New diagnostic routine

新的诊断程序

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

我们需要知道导致问题的单元格 A1 中的字符。

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

End Sub

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

转到 VB 编辑器的立即窗口并输入以下文本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.

在此行下方，您应该会看到类似54 65 73 74 31. 这是单元格中每个字符的代码值的列表。我希望我们会在列表的末尾看到A换行代码或D回车代码。

Position the cursor in cell A1. Click F2to select edit then Backspaceto delete the invisible trailing character then Returnto 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.

将光标定位在单元格 A1 中。单击F2以选择编辑，然后Backspace删除不可见的尾随字符，然后Return结束编辑。返回立即窗口，将光标定位到末尾DsplInHex(Sheets("Sheet1").range("A1"))并单击Return。尾随字符应该已经消失了。

Try that and report back. Good luck.

试试那个然后报告。祝你好运。