copying from another instance of excel

[Paul]

Is it possible to copy cell contents between instances of
excel??

I cannot get the following code to work. Can anyone tell
me why?

Application.Workbooks("book2.xls").Worksheets(Sheet1).Range
("a1").Value = Application.Workbooks
("book1.xls").Worksheets(Sheet1).Range("a1").Value

 

[Ray Costanzo [MVP]]

Do you get an error? What does it say?

My guess is that your problem is with (Sheet1). When using
Workbook.Worksheets, the argument expected is either the numeric
index of the worksheet or the name property of the sheet - which is a
string. So, try using ("Sheet1") instead (Sheet1).

Ray at work

 

[Bob Phillips]

Do you really mean separate instances of Excel or separate workbooks? If the
latter, you need to enclose the worksheet name in quotes, and you don't need
Application, it is implicit.

Workbooks("book2").Worksheets("Sheet1").Range("a1").Value = _
Workbooks("book1").Worksheets("Sheet1").Range("a1").Value


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks Ray,

Tried that but with no joy. also tried (1) in place of
("sheet1")

here is the result of f1

Application Property
See AlsoApplies ToExampleSpecificsWhen used without an
object qualifier, this property returns an Application
object that represents the Microsoft Excel application.
When used with an object qualifier, this property returns
an Application object that represents the creator of the
specified object (you can use this property with an OLE
Automation object to return the application of that
object). Read-only.

expression.Application
expression Required. An expression that returns one of
the above objects.

Example
This example displays a message about the application that
created myObject.

Set myObject = ActiveWorkbook
If myObject.Application.Value = "Microsoft Excel" Then
MsgBox "This is an Excel Application object."
Else
MsgBox "This is not an Excel Application object."
End If

 

[Paul]

Thanks Bob,

I am running two instances of excel, i.e. both are running
on the same machine. one is book1.xls and the other is
book2.xls. Have corrected the quotes problem, but still
cannot get it to work. Aslo have tried to set a variable
to the value --- also with no luck.

 

[Ray Costanzo [MVP]]

If they're book1.xls, use "book1.xls" and "book2.xls," not "book1"
and "book2."

Ray at work

 

[Bob Phillips]

Paul,

Book1 and book2 are workbooks, they are not necessarily separate instances.

If you have two instances of Excel, I assume the code is in one of them. To
access the other, you need to go via an application object. But, how do you
get a handle to the other on e? AFAIK, when setting a variable to an
instance of Excel, you can not pre-determine which you will get. So you
might just end up with the same instance as you are in.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Assuming you have a controlled environment (i.e., the other instance of XL is
not automagically instantiated), the following tested code will do the job.

Option Explicit

Sub testIt()
#Const doCleanup = False
Dim otherXL As Application, thisXLWB As Workbook, _
otherXLWB As Workbook
Set otherXL = CreateObject("excel.application")
otherXL.Visible = True
Set otherXLWB = otherXL.Workbooks.Add()
otherXLWB.Worksheets(1).Range("a1").Resize(10, 3).Formula = _
"=row()/column()"
Set thisXLWB = Application.Workbooks.Add()
'...
otherXLWB.Worksheets(1).Range("a1").CurrentRegion.Copy
thisXLWB.Sheets(1).Range("a1").PasteSpecial xlValues
'...
#If doCleanup Then
otherXLWB.Close False
otherXL.Quit
Set otherXLWB = Nothing
Set otherXL = Nothing
#End If
End Sub