在两个Excel实例之间复制范围 [英] Copy Range between two instances of excel

问题描述

我正在运行两个单独的Excel实例，并且试图将数据从一个工作簿中的Range复制到另一个工作簿中.

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

我有此代码:

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

仍然不执行任何操作

我的VBA技能不是很好，两周前才开始学习它.

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

推荐答案

所以我设法使我的代码正常工作，问题出在句柄上，对此进行了更改:

So I managed to get my code working, the issue was with the handle, changed this:

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

到

Set xlApp = GetObject("c:\mypath\book1.xlsm").Application

还更改了使用上一个答案中的建议进行复制的方法

And also changed the method of copying using the suggestion from the previous answer

因此，面对此问题的任何人的完整工作代码为:

So the full working code for anyone facing this issue is:

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

Set xlApp = GetObject("c:\mypath\book1.xlsm").Application

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

Vals = Src
Dst.Value = Vals
End Sub

谢谢大家的帮助.

这篇关于在两个Excel实例之间复制范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！