vba VB：将范围复制到另一个 Excel 应用程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8560212/
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: Copy a Range to another Excel Application
提问by Mic
I'm trying to solve a relatively simple problem but I can't realize it. My aim is to copy a range of cells in a worksheet of the main Excel Application to another range (of the same size) in a worksheet of a second newly created Excel Application. I create the second Application by using
我正在尝试解决一个相对简单的问题，但我无法意识到。我的目标是将主 Excel 应用程序的工作表中的一系列单元格复制到第二个新创建的 Excel 应用程序的工作表中的另一个区域（相同大小）。我使用创建第二个应用程序
Set secondExApp = CreateObject("Excel.Application")
I'm using this reference for further handling. Until now I've tried two different ways. Both don't work properly.
我正在使用此参考进行进一步处理。到目前为止，我已经尝试了两种不同的方法。两者都不能正常工作。
0.: Preparation / Introduction
0.：准备/介绍
Set srcWb = Application.ActiveWorkbook
Set srcSheet = srcWb.Worksheets("example")
Set dstApp = CreateObject("Excel.Application")
Set dstWb = dstApp.Workbooks(1)
Set dstSheet = dstWb.Worksheets(1)
1.: PasteSpecial - delivers an image(!)instead of just the range
1.：PasteSpecial - 提供图像（！）而不仅仅是范围
srcSheet.Range("A1:B2").Copy
dstSheet.Range("A1:B2").PasteSpecial xlPasteAll
2.: Range.Copy [Destination] - does not work - Is it right that I can only use this method for sheets in the same application?
2.：Range.Copy [Destination] - 不起作用 - 我只能对同一应用程序中的工作表使用此方法对吗？
srcSheet.Range(srcSheet.Cells(..., ...), srcSheet.Cells(..., ...)).Copy _
dstSheet.Range(dstSheet.Cells(..., ...), dstSheet.Cells(..., ...))
Any help is appreciated.
任何帮助表示赞赏。
Edit: I've already played with the "record macro" functionality but I prefer coding it on my own without "selecting" or "activating" cells / sheets / etc.
编辑：我已经玩过“记录宏”功能，但我更喜欢自己编码而不是“选择”或“激活”单元格/工作表/等。
Edit (solved):
Thank you both GSerg and iDevlop very much, you delivered a good further starting point for me. I did some research as far as the Excel constants as xlClipboardFormatDspTextare concerned.
编辑（已解决）：非常感谢 GSerg 和 iDevlop，你们为我提供了一个很好的起点。就Excel常量xlClipboardFormatDspText而言，我做了一些研究。
What really helped me was the fact that opening a new Excel instance changes the Paste(Special) menu.
真正帮助我的是打开一个新的 Excel 实例会更改粘贴（特殊）菜单。
So instead of creating a new instance I now simply add a workbook (which can be hidden) and use this object to add my content. Since it is held in the same instance (also have a look at the task manager) the Paste(Special) menu is completely the same.
因此，我现在无需创建新实例，只需添加一个工作簿（可以隐藏）并使用此对象添加我的内容。由于它保存在同一个实例中（也可以查看任务管理器），因此粘贴（特殊）菜单完全相同。
Now it is possible to use Range.Copy [destination] even without select!
现在即使没有选择也可以使用 Range.Copy [destination]！
Result:
结果：
'Hides the new workbook
Application.ScreenUpdating = False
Set dstWb = Workbooks.Add
Set dstSheet = dstWb.Worksheets(1)
srcSheet.Range(srcSheet.Cells(..., ...), srcSheet.Cells(..., ...)).Copy
dstSheet.Paste dstSheet.Range(dstSheet.Cells(..., ...), dstSheet.Cells(..., ...))
'Avoids the often seen dashed border around the copied range
Application.CutCopyMode = False
'Setting the initial change back
Application.ScreenUpdating = True
Software: Excel 2007
软件：Excel 2007
回答by GSerg
As determined by poking Excel with a stick, you have to use Worksheet.Pastefor inter-excel stuff:
通过用棍子戳 Excel 确定，您必须使用Worksheet.Pasteexcel 间的东西：
srcSheet.Range("A1:B2").Copy
dstSheet.Paste dstSheet.Range("A1")
Poking Excel with a thicker stick revealed that formulas get preserved when pasting from Clipboard as xlClipboardFormatDspText:
用较粗的棍子戳 Excel 显示，从剪贴板粘贴时，公式会被保留为xlClipboardFormatDspText：
srcSheet.Range("A1:B2").Copy
dstSheet.Range("A1").Select
dstSheet.PasteSpecial xlClipboardFormatDspText, False
However, this does require selecting a cell on dstSheet first, because Worksheet.PasteSpecialuses active cell.
但是，这确实需要先在 dstSheet 上选择一个单元格，因为Worksheet.PasteSpecial使用的是活动单元格。
