Copy Range between two instances of excel

Question

I'm running two separate instances of Excel and I'm trying to copy data from a Range in one workbook to the other.

I have this code:

Sub CopyValues()
Dim xlApp As Excel.Application
Set xlApp = GetObject(, "Excel.Application")
Dim Src As Range
Dim Dst As Range
    Set Src = xlApp.ActiveSheet.Range("A1:A9")
    Set Dst = Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1:A9")
    Src.Copy Dst
End Sub

It doesn't return any errors but it doesn't copy the values, Also tried this for the last line

Src.Value = Dst.Value

Still does nothing

My VBA skills are not so good, just started learning it 2 weeks ago.

Answer 1

If you want to avoid using the clipboard, and assuming that your handle to the other instance of Excel (xlApp) is correct, then you should be able to use an array to get and set your data.

Sub CopyValues()
    Dim xlApp As Excel.Application
    Dim Src As Range
    Dim Dst As Range
    Dim Vals() as Variant

    Set xlApp = GetObject(, "Excel.Application")

    Set Src = xlApp.ActiveSheet.Range("A1:A9")
    Set Dst = Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1:A9")

    Vals = Src
    Dst.Value = Vals
End Sub