VB：将范围复制到另一个Excel应用程序 [英] VB: Copy a Range to another Excel Application

问题描述

我正在尝试解决一个比较简单的问题，但我无法意识到这一点。我的目的是将主Excel应用程序的工作表中的单元格范围复制到第二个新创建的Excel应用程序的工作表中的另一个范围（大小相同）。我创建第二个应用程序使用

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

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：准备/简介

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 - 提供一个图像（！），而不仅仅是范围

1.: PasteSpecial - delivers an image(!) instead of just the range

srcSheet.Range("A1:B2").Copy
dstSheet.Range("A1:B2").PasteSpecial xlPasteAll

2：Range.Copy [Destination] - 不工作 - 是否正确的，我只能在同一个应用程序中使用此方法的表格？

2.: Range.Copy [Destination] - does not work - Is it right that I can only use this method for sheets in the same application?

srcSheet.Range(srcSheet.Cells(..., ...), srcSheet.Cells(..., ...)).Copy _
dstSheet.Range(dstSheet.Cells(..., ...), dstSheet.Cells(..., ...))

任何帮助都不胜感激。

编辑：
I已经使用记录宏功能播放，但是我喜欢自己编码，而不需要选择或激活单元格/表格/等。

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.

编辑（已解决）：
非常感谢GSerg和iDevlop，您为我提供了一个更好的起点。对于 xlClipboardFormatDspText 的Excel常量，我做了一些研究。

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 xlClipboardFormatDspText are concerned.

真正帮助我的是事实打开一个新的Excel实例会改变粘贴（特殊）菜单。

What really helped me was the fact that opening a new Excel instance changes the Paste(Special) menu.

所以，我现在只需添加一个工作簿（可以隐藏）并使用这个对象添加我的内容。由于它是在同一个实例中（也可以看看任务管理器），粘贴（特殊）菜单是完全一样的。

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.

现在可以使用

结果：

'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

---

软件：Excel 2007

---

Software: Excel 2007

推荐答案

由poking Excel用一个棒，你必须使用 Worksheet.Paste for inter-excel东西：

As determined by poking Excel with a stick, you have to use Worksheet.Paste for inter-excel stuff:

srcSheet.Range("A1:B2").Copy
dstSheet.Paste dstSheet.Range("A1")

---

使用较厚的粘贴表示Excel表示，从剪贴板粘贴时，公式会保留为 xlClipboardFormatDspText ：

srcSheet.Range("A1:B2").Copy
dstSheet.Range("A1").Select
dstSheet.PasteSpecial xlClipboardFormatDspText, False

但是，这样做首先需要在dstSheet上选择一个单元格，因为 Worksheet.PasteSpecial 使用活动单元格。

However, this does require selecting a cell on dstSheet first, because Worksheet.PasteSpecial uses active cell.

这篇关于VB：将范围复制到另一个Excel应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！