如何在 Excel 中仅复制单元格的纯文本? [英] How to copy only plain text of cells in Excel?

问题描述

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

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 中有没有一种方法可以使用 VBA 仅复制所选单元格的纯文本?没有格式，没有表格或单元格边框，只有文本，没有别的.我当前的解决方法宏是复制单元格，打开记事本，粘贴到记事本，然后从记事本复制以获取纯文本.这是非常不可取的，我希望有一种方法可以在 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!

推荐答案

像这样?

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

或者

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

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

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

同样适用于 Range 对象.

编辑

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

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

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

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

but most developers avoid SendKeys because 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, SendKeys opens 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..

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

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

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

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

这篇关于如何在 Excel 中仅复制单元格的纯文本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！