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

问题描述

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

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 copies the entire part, but we can control what is pasted.

范围对象。

编辑

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屋！