如何在VBA中的两个打开的Excel实例之间进行复制？

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是不够的。

2 个解决方案

#1

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:

在类似的情况下，我保持简单，并制作两个按钮：

Export that saves to clipboard.csv or clipboard.xlsx or whatever format you like the data to copy
导出保存到clipboard.csv或clipboard.xlsx或您喜欢要复制的数据的任何格式
Import that gets the data from the clipboard temporary file
从剪贴板临时文件中获取数据的导入

The user is in charge of clicking on the Export button on one instance, then on the Import button on the second instance.

用户负责单击一个实例上的“导出”按钮，然后单击第二个实例上的“导入”按钮。

#2

I think you need to elaborate as to why you need separate 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

#1