Assigning a cell value to a variable - between different workbooks

[jpor]

How, without opening the source workbook, copy the contents of a cell and put it in a variable in the destination workbook?

Example:

valueX = "='c:\code\[example.xlsx]Sheet1'!R2C1"
MsgBox (valueX)

The result is ='c:\code\[example.xlsx]Sheet1'!R2C1
and not the value XPTO

If I assign the reference to a cell directly, it work:

Range("A1").Value="='c:\code\[example.xlsx]Sheet1'!R2C1"
MsgBox(Range("A1").Value)

The result is XPTO

[Kevin#]

This line of code assigns text to variable named valueX

valueX = "='c:\code\[example.xlsx]Sheet1'!R2C1"

The Evaluate function in VBA is what you need
The formula you put in a cell to sum the contents of A25 to A28 is
=SUM(A25:A28)

VBA can do the same thing if you wrap Evaluate() around it.

Here the message box will show the value of that sum

 MsgBox Evaluate("SUM(A25:A28)")

[SamT]

In VBA you want to use

valueX = [Blah,Blah]("c:\code\[example.xlsx]Sheet1.Cells(2,1)")

[jpor]

But, how do I apply to my example?

valueX = Evaluate("='c:\code\[example.xlsx]Sheet1'!R2C1")

does not work!

[jpor]

Sorry, but I did not understand the example [Blah,Blah]

valueX = [Blah,Blah]("c:\code\[example.xlsx]Sheet1.Cells(2,1)")

[Kevin#]

@jpor I did not read your question properly. Oops! Evaluate only works if the other workbook is also open.
For what it's worth, this should give you XPTO if the other workbook is open when you run it.

X = Evaluate("[example.xlsx]Sheet1!A2").Value
MsgBox X