PDA

View Full Version : Assigning a cell value to a variable - between different workbooks



jpor
01-18-2016, 11:42 AM
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#
01-18-2016, 12:10 PM
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
01-18-2016, 02:37 PM
In VBA you want to use

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

jpor
01-19-2016, 02:48 AM
But, how do I apply to my example?



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


does not work!

jpor
01-19-2016, 02:51 AM
Sorry, but I did not understand the example [Blah,Blah]



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

Kevin#
01-19-2016, 07:55 AM
@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