9

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.

CRABOLO
  • 8,605
  • 39
  • 41
  • 68
Ovidiu Nacov
  • 101
  • 1
  • 6
  • 2
    Do you actually have multiple instances of Excel open, or just multiple files open in one instance? – Degustaf Dec 31 '14 at 15:00
  • 2 things: Why are you using `ActiveSheet`? You should define the Sheet for `Src`. Also, try `Src.Copy` and `Dst.PasteSpecial xlPasteValues`? – Chrismas007 Dec 31 '14 at 15:02
  • Yes I have multiple instances of excel running and there is no way around this unfortunately. – Ovidiu Nacov Dec 31 '14 at 15:09
  • Also need to use ActiveSheet for the source workbook switches between sheets on an interval. I tried replacing that with a specific worksheet with the same result. Would prefer not using the clipboard for the copying, but tried your suggestion and it pastes an empty cell. Seems it's got an issue woth the copying, it might not be copying anything. – Ovidiu Nacov Dec 31 '14 at 15:11
  • 1
    Could you supply the version of Excel? XL2013 treats application windows and instances a little different than earlier versions. –  Dec 31 '14 at 15:14
  • I'm using excel 2007 – Ovidiu Nacov Dec 31 '14 at 15:21
  • Does Changing `Set Dst = Workbooks(...` to `Set Dst = xlApp.Workbooks(...` fix the issue? – Degustaf Dec 31 '14 at 15:23
  • Are you sure that `xlApp` is actually your other instance of Excel? – Degustaf Dec 31 '14 at 15:25
  • You could try using the Windows clipboard with a dataobject: http://www.cpearson.com/excel/Clipboard.aspx – Doug Glancy Dec 31 '14 at 15:28
  • Well I tried Set xlApp = GetObject("Book2.xlsm").Application to be sure, still does nothing – Ovidiu Nacov Dec 31 '14 at 15:29
  • Try `xlApp.ActiveSheet.Range("A1").value = "This is xlApp"`. See which instance this occurs in. – Degustaf Dec 31 '14 at 15:31
  • Going to have a look at that DataObject method described in your link. Just don't understand why the code does nothing, doesn't seem to be anything wrong with it, but I don't have much experience si who know – Ovidiu Nacov Dec 31 '14 at 15:33
  • Tried your suggestion Degustaf, it doesn't change the cell value in either of the two Workbooks. Seems that this code has a fundamental flaw somewhere, I just don't know where. – Ovidiu Nacov Dec 31 '14 at 15:48
  • 1
    Using `GetObject` is never a good way to capture multiple instances of a program. You need to control the opening of the second instance. Why is having two instances unavoidable? – Mr. Mascaro Dec 31 '14 at 15:51
  • The books I'm working on are opened by a third party program I'm using, and it opens the files in separate instances. – Ovidiu Nacov Dec 31 '14 at 15:55
  • Are all of the files different names? If so, try using `Set xlApp = GetObject("Book1.xlsm").Application` – Degustaf Dec 31 '14 at 16:05
  • Strange, tried that before and nothing happened, now it gives a Run-Time error. Automation error Invalid syntax. – Ovidiu Nacov Dec 31 '14 at 16:08
  • Fyi, using the @ before a user's name, e.g., @DougGlancy, will let them know you responded. – Doug Glancy Dec 31 '14 at 16:20
  • @DougGlancy, Thanks for the tip, new around here so I appreciate all the suggestions – Ovidiu Nacov Dec 31 '14 at 16:21
  • Well, gonna leave this problem for next year, Happy New Year everyone – Ovidiu Nacov Dec 31 '14 at 17:18
  • Just spitballing here but what if you change "Activesheet" to Worksheets("name") – CaptainABC Dec 31 '14 at 18:36
  • 2
    For dealing with multiple instances see http://stackoverflow.com/questions/2971473/can-vba-reach-across-instances-of-excel – brettdj Jan 01 '15 at 02:21

2 Answers2

2

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
phrebh
  • 159
  • 2
  • 4
  • 13
  • Still does nothing, so I assume that my handle is incorrect. So this is my main issue, how to get xlApp to point to the correct range. Also when I tried useing Src.Copy and Dst.PasteSpecial xlPasteValues it pasted empty cells. Btw how do I insert code into comments? – Ovidiu Nacov Dec 31 '14 at 17:51
  • @pnuts yea for short pieces of code, but how? Like in my previous comment "Src.Copy and Dst.PasteSpecial xlPasteValues" – Ovidiu Nacov Dec 31 '14 at 18:06
1

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

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

to

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

Thank you all for your help.

Ovidiu Nacov
  • 101
  • 1
  • 6