View Full Version : Assigning a cell value to a variable - between different workbooks
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)")
In VBA you want to use
valueX = [Blah,Blah]("c:\code\[example.xlsx]Sheet1.Cells(2,1)")
But, how do I apply to my example?
valueX = Evaluate("='c:\code\[example.xlsx]Sheet1'!R2C1")
does not work!
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.