如何在 VBA 中的两个打开的 Excel 实例之间进行复制？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7867939/
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
How can I copy between two open Excel instances in VBA?
提问by Prabu Mike
I want to copy data from one already opened instance of Excel to another instance of Excel in VBA. I tried:
我想将数据从一个已打开的 Excel 实例复制到 VBA 中的另一个 Excel 实例。我试过：
Option Explicit
Sub copy_paste()
Dim destination_sanitized As String
Dim fs As New FileSystemObject
destination_sanitized = fs.BuildPath("c:\temp\", "1.xlsx")
Dim xl As New Excel.Application
Dim wb As Workbook
Set wb = xl.Workbooks.Open(Filename:=destination_sanitized)
Dim r1 As Range
Dim r2 As Range
Set r1 = ThisWorkbook.Sheets("hidden").Range("E10:E13")
Set r2 = wb.Sheets("Sheet1").Range("J20:J23")
On Error GoTo Cleanup
r1.Copy r2
Cleanup:
wb.Close SaveChanges:=False
Set xl = Nothing
MsgBox Err.Number & ": " & Err.description
End Sub
I get Run-time error '1004': Copy method of Range class failed
我收到运行时错误“1004”：Range 类的复制方法失败
How can I copy data from one already opened instance of Excel to another Excel instance in VBA?
如何将数据从一个已打开的 Excel 实例复制到 VBA 中的另一个 Excel 实例？
I understand how to do this when they are part of the same instance. In this particular case, I need the two workbooks to be in separate instances. I also need to do a full copy (Data Validation, Formulas, Values, Formats, etc), so r2.Value = r1.Value will not suffice.
当它们属于同一实例时，我了解如何执行此操作。在这种特殊情况下，我需要将两个工作簿放在不同的实例中。我还需要做一个完整的副本（数据验证、公式、值、格式等），所以 r2.Value = r1.Value 是不够的。
回答by Sam
I think you need to elaborate as to why you needseparate instances, so far in my career I have never had any reason to use a separate instance in Excel and it is a nightmare for automation.
我认为您需要详细说明为什么需要单独的实例，到目前为止，在我的职业生涯中，我从未有任何理由在 Excel 中使用单独的实例，这对自动化来说是一场噩梦。
That being said, you can give something like this a try (assuming you only have 2 instances open):
话虽如此，您可以尝试这样的操作（假设您只打开了 2 个实例）：
Sub MM()
Dim varTask As Variant
Dim XL1 As Application, XL2 As Application
Dim r1 As Range, r2 As Range
Dim OtherWB As Workbook
Dim destination_sanitized As String
destination_sanitized = CreateObject("Scripting.FileSystemObject").BuildPath("C:\temp\", "1.xlsx")
With CreateObject("Word.Application")
If .Tasks.Exists("Microsoft Excel") Then
For Each varTask In .Tasks
Debug.Print varTask
If InStr(varTask.Name, "Microsoft Excel") = 1 Then
If XL1 Is Nothing Then
Set XL1 = GetObject(Replace(varTask, "Microsoft Excel - ", "")).Application
Else
Set XL2 = GetObject(Replace(varTask, "Microsoft Excel - ", "")).Application
End If
End If
Next varTask
End If
.Quit
End With
'Then something like...
Set r1 = ThisWorkbook.Sheets("hidden").Range("E10:E13")
Set OtherWB = XL2.Workbooks.Open(destination_sanitized)
Set r2 = OtherWB.Sheets("Sheet1").Range("J20:J23")
r1.Copy r2
'Clear down memory afterwards
Set r1 = Nothing
Set r2 = Nothing
OtherWB.Close False
Set OtherWB = Nothing
Set XL1 = Nothing
XL2.Quit
Set XL2 = Nothing
End Sub
回答by stenci
It is difficult to get two instances of Excel to talk to each other in all situations. You can find the other running instances, but there are too many things to consider.
很难让两个 Excel 实例在所有情况下都相互交谈。您可以找到其他正在运行的实例，但要考虑的事情太多了。
In similar cases I keep it simple and make two buttons:
在类似的情况下，我保持简单并制作两个按钮：
- Exportthat saves to clipboard.csv or clipboard.xlsx or whatever format you like the data to copy
- Importthat gets the data from the clipboard temporary file
- 导出保存到 clipboard.csv 或 clipboard.xlsx 或您喜欢复制数据的任何格式
- 从剪贴板临时文件中获取数据的导入
The user is in charge of clicking on the Export button on one instance, then on the Import button on the second instance.
用户负责在一个实例上单击“导出”按钮，然后在第二个实例上单击“导入”按钮。
