How can I copy between two open Excel instances in VBA?

Question

I want to copy data from one already opened instance of Excel to another instance of Excel in VBA. I tried:

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

How can I copy data from one already opened instance of Excel to another Excel instance in VBA?

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.

Answer 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.

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
• 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.