vba 如何仅复制Excel中单元格的纯文本？

How to copy only plain text of cells in Excel?

提问by Eli Greenberg

I am designing an Excel worksheet where the user will click a command button which copies a predetermined range of cells. The user would then paste the contents into a web app using Firefox or IE. The design of the web app is out of my control and currently the text boxes that are used for data input are rich text inputs. This causes the text to look odd and formatted like Excel when the user pastes into them.

我正在设计一个 Excel 工作表，用户将在其中单击一个命令按钮来复制预定范围的单元格。然后，用户将使用 Firefox 或 IE 将内容粘贴到 Web 应用程序中。Web 应用程序的设计不受我的控制，目前用于数据输入的文本框是富文本输入。当用户粘贴到文本中时，这会导致文本看起来很奇怪并且格式类似于 Excel。

Is there a way in Excel using VBA to copy only the plain text of the cells that are selected? No formatting, no tabling or cell borders, just the text and nothing else. My current workaround macro is copying the cells, opening Notepad, pasting into Notepad, and then copying from Notepad to get the plain text. This is highly undesirable and I'm hoping there's a way to do this within Excel itself. Please let me know, thanks!

有没有办法在 Excel 中使用 VBA 仅复制所选单元格的纯文本？没有格式，没有表格或单元格边框，只有文本，没有别的。我当前的解决方法宏是复制单元格，打开记事本，粘贴到记事本，然后从记事本复制以获取纯文本。这是非常不可取的，我希望有一种方法可以在 Excel 本身中做到这一点。请让我知道，谢谢！

回答by Ioannis

Something like this?

像这样的东西？

Sheet1.Cells(1, 1).Copy
Sheet1.Cells(1, 2).PasteSpecial xlPasteValues

Or

或者

selection.Copy
Sheet1.Cells(1,2).Activate
Selection.PasteSpecial xlPasteValues

Copycopies the entire part, but we can control what is pasted.

Copy复制整个部分，但我们可以控制粘贴的内容。

Same applies to Rangeobjects as well.

这同样适用于Range对象。

EDIT

编辑

AFAIK, there is no straighforward way to copy only the text of a range without assigning it to a VBA object (variable, array, etc.). There is a trick that works for a single cell and for numbers and text only (no formulas):

AFAIK，没有直接的方法可以只复制范围的文本而不将其分配给 VBA 对象（变量、数组等）。有一个技巧适用于单个单元格，仅适用于数字和文本（无公式）：

Sub test()
    Cells(1, 1).Select
    Application.SendKeys "{F2}"
    Application.SendKeys "+^L"
    Application.SendKeys "^C"
    Cells(1, 3).Select
    Application.SendKeys "^V"
End Sub

but most developers avoid SendKeysbecause it can be unstable and unpredictable. For example, the code above works only when the macro is executed from excel, not from VBA. When run from VBA, SendKeysopens the object browser, which is what F2 does when pressed at the VBA view :) Also, for a full range, you will have to loop over the cells, copy them one by one and paste them one by one to the application. Now that I think better, I think this is an overkill..

但大多数开发人员会避免，SendKeys因为它可能不稳定且不可预测。例如，上面的代码仅在从 excel 执行宏时才有效，而不是从VBA. 从 运行时VBA，SendKeys打开对象浏览器，这就是在 VBA 视图中按下 F2 时所做的 :) 此外，对于完整范围，您必须遍历单元格，将它们一一复制并一一粘贴到应用程序。现在我想得更好，我认为这是一种矫枉过正..

Using arrays is probably better. This one is my favorite reference on how you pass ranges to vba arrays and back: http://www.cpearson.com/excel/ArraysAndRanges.aspx

使用数组可能更好。这是我最喜欢的关于如何将范围传递给 vba 数组并返回的参考：http: //www.cpearson.com/excel/ArraysAndRanges.aspx

Personally, I would avoid SendKeysand use arrays. It should be possible to pass the data from the VBAarray to the application, but hard to say without knowing more about the application..

就个人而言，我会避免SendKeys使用数组。应该可以将VBA数组中的数据传递给应用程序，但是如果不了解应用程序的更多信息就很难说。

回答by Dan

Actually, the best way to do this is to copy the cells and paste into a notepad. Notepad won't recognize the cells. You can then copy the text back into whatever cell you want. This works for copying text from multiple cells into a single cell.

实际上，最好的方法是复制单元格并粘贴到记事本中。记事本无法识别单元格。然后，您可以将文本复制回您想要的任何单元格。这适用于将文本从多个单元格复制到单个单元格中。

回答by Peter

In Excel, highlight the cell in question. Hit F2. CTRL+Shift+Home. (This highlights the cell's entire contents.) CTRL+C. Go to destination application. CTRL+V. It looks like a lot of steps, but when you actually do it, it's much quicker than using the ribbons to accomplish the same.

在 Excel 中，突出显示有问题的单元格。按F2。CTRL+Shift+Home。（这会突出显示单元格的全部内容。）CTRL+C。转到目标应用程序。CTRL+V。看起来像很多步骤，但当你真正做到时，它比使用色带来完成同样要快得多。

If you need to copy multiple cells into an application bereft of the Paste Special… facility, then do a regular copy and paste from Excel into Notepad, and then do a copy and paste from Notepad to the destination. Cumbersome, but it works.

如果您需要将多个单元格复制到没有“选择性粘贴...”功能的应用程序中，请从 Excel 定期复制并粘贴到记事本，然后从记事本复制并粘贴到目标位置。麻烦，但它有效。

回答by Lee

If you're dealing with a lot of cells to be copied, the selection.copy method will be extremely slow. (I experienced that when running a macro on 200 000 records).

如果您要处理大量要复制的单元格，则 selection.copy 方法将非常慢。（我在 200 000 条记录上运行宏时遇到过这种情况）。

A 100 times more performant way is to directly assign the value of one cell to another. Example from my code:

性能提高 100 倍的方法是直接将一个单元格的值分配给另一个单元格。我的代码中的示例：

With errlogSheet
         'Copy all data from the current row

          reworkedErrorSheet.Range("A" & reworkedRow).Value = .Range("A" & currentRow).Value
          reworkedErrorSheet.Range("B" & reworkedRow).Value = .Range("B" & currentRow).Value
          reworkedErrorSheet.Range("C" & reworkedRow).Value = .Range("C" & currentRow).Value
          reworkedErrorSheet.Range("D" & reworkedRow).Value = .Range("D" & currentRow).Value
          reworkedErrorSheet.Range("E" & reworkedRow).Value = .Range("E" & currentRow).Value

回答by Yukio Kikuchi

This can be easily solved without bothering with VBA.

这可以轻松解决，而无需使用 VBA。

The user can paste the contents of the clipboard by Ctrl+ Shift+ Vinstead of more usual Ctrl+ V(pasting as formatted).

用户可以通过Ctrl+ Shift+V而不是更常见的Ctrl+ V（按格式粘贴）粘贴剪贴板的内容。

Ctrl+ Shift+ Vpastes the clipboard content as plain text.

Ctrl+ Shift+V将剪贴板内容粘贴为纯文本。

回答by qwerty

In Excel 2013 you can do this with shortcuts.

在 Excel 2013 中，您可以使用快捷方式执行此操作。

Press Ctrl+ Alt+ Vto open the paste special window. Now you can click the values radio button or just press Vif your Excel is in English. If you don't use Excel in English you can see which button can be pressed to select the wanted option by looking at the underlining of the single letters.

按Ctrl+ Alt+V打开粘贴特殊窗口。现在您可以单击值单选按钮，或者V如果您的 Excel 是英文的，则只需按一下。如果您不使用英文 Excel，您可以通过查看单个字母的下划线来查看可以按下哪个按钮来选择所需的选项。

Finaly press Enterto paste your copied selection.

最后按Enter粘贴您复制的选择。