vba VB Excel PasteSpecial 需要剪贴板内容吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10336805/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VB Excel PasteSpecial requiring clipboard content?
提问by dozacinc
I'm having a problem with VB PasteSpecial.
This code works perfectly in Excel VB (given that you have selected cells with data)
我在使用 VB PasteSpecial 时遇到了问题。
此代码在 Excel VB 中完美运行（假设您选择了带有数据的单元格）
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
However, I'm using a third-party software (QlikView) that I extract the data from, which is then supposed to be copied into the Excel document. There is no problem with the normal paste but it MUSTbe transposed.
但是，我使用的是从中提取数据的第三方软件 (QlikView)，然后应该将其复制到 Excel 文档中。正常粘贴没有问题，但必须转置。
Obviously, since I dont have any content in the workbook to copy, I don't use
显然，由于我没有工作簿中的任何内容要复制，因此我不使用
Selection.Copy
But because I don't copy anything from the document first (even though there are table data in the copy memory), this call returns bad argument exception (this also happens if I copy cells in that VERY workbook first and then just call the macro for transposing it).
但是因为我没有先从文档中复制任何东西（即使复制内存中有表格数据），这个调用返回错误的参数异常（如果我先复制那个非常工作簿中的单元格然后只调用宏，也会发生这种情况用于转置它）。
Runtime error '1004' returned. PasteSpecial method of Range class failed.
返回运行时错误“1004”。Range 类的 PasteSpecial 方法失败。
Yes, I can paste it into the document, then cut it from the area, move it to the correct place and transpose it, but that is bad coding.
是的，我可以将它粘贴到文档中，然后从该区域剪切它，将其移动到正确的位置并转置它，但这是错误的编码。
Have any of you experienced this and got a way to get this working ?
你们中有没有人经历过这种情况并有办法让它发挥作用？
回答by Siddharth Rout
You will have to use the method as you mentioned above. You can also try this
您将不得不使用上面提到的方法。你也可以试试这个
Range("A1").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Application.CutCopyMode = False
But you will have to copy it again and transpose it. Other wise there is no direct way you can transpose it.
但是您将不得不再次复制并转置它。否则，没有直接的方法可以转置它。
回答by i_saw_drones
The reason that you cannot use PasteSpecial to transpose the data is due to the format of the data as it resides in the clipboard after you copy it from QlikView.
您不能使用 PasteSpecial 转置数据的原因是数据的格式，因为它在您从 QlikView 复制后驻留在剪贴板中。
When you copy data from a QlikView table (which I assume you are copying from), it copies it to the clipboard in three formats: HTML, Unicode and standard (code-paged) text:
当您从 QlikView 表（我假设您是从中复制）复制数据时，它会以三种格式将其复制到剪贴板：HTML、Unicode 和标准（代码分页）文本：
Comparing this with Excel's clipboard formats:
将此与 Excel 的剪贴板格式进行比较：
As you can see, when copying data in Excel, it stores the data in the clipboard in its own format and as such knows how to transpose the cells if required. For QlikView, the clipboard just contains plain text, therefore Excel does not know how to transpose this and as a result the PasteSpecial call fails.
如您所见，在 Excel 中复制数据时，它以自己的格式将数据存储在剪贴板中，因此知道如何在需要时转置单元格。对于 QlikView，剪贴板仅包含纯文本，因此 Excel 不知道如何转置它，因此 PasteSpecial 调用失败。
If you are copying from a table in QlikView to Excel, I would recommend performing the transposition already in QlikView if you can by using a "Pivot Table" chart in QlikView (as you can drag the columns and rows around how you wish). Otherwise you will have to use Siddharth's code and transpose it once it's in Excel.
如果您要从 QlikView 中的表格复制到 Excel，如果可以的话，我建议您在 QlikView 中执行已经在 QlikView 中的转置（因为您可以在 QlikView 中使用“数据透视表”图表（因为您可以随意拖动列和行）。否则，您将不得不使用 Siddharth 的代码并在 Excel 中将其转置。
