Fetching value for other workbook

[Adrian]

One of my workbooks has a macro, workin well enough except
for one thing.
It has to fetch a namevalue (not a cell) from another
workbook, which is already open, and I can't get it to
happen. (For now I have hardcoded the value into the
macro.)

The code I have tried looks like this....
txt1 & workbook.xls!name & txt2
(Run time error 424, object required)

For now it is hardcoded like this....
txt1 & "03" & txt2
and all the rest of the macro works fine.

 

[Ian Coates]

If you are calling a specific cell on another workbook from within excell,
you need to state filename, sheetname and cell reference. Do you need to
include the sheet name as well as the filename in you code?

 

[Adrian]

It's not a cell - it's a named value. These things are, I
believe, contained in the workbook not in a sheet.
On a sheet, even a sheet in another book, I can refer to
the value with
=workbook.xls!name
but that doesn't work in the VBA. I've also tried
workbook.xls!name.value in the VBA but that doesn't work
either.

 

[Tom Ogilvy]

If the name contains a constant:

Dim sStr as String
Dim vVal as variant

sStr = workbooks("workbook2.xls").Names("Name").RefersTo
vVal = Evalute(sStr)
msgbox vVal & " " & typename(vVal)

if the name refers to a cell or range

Dim rng as Range
set rng = Workbooks("workbook2.xls").Names("Name").RefersToRange