I don't know if this will work for your particular situation, but there is a
method documented at John Walkenbach's site:
A VBA Function to Get a Value From a Closed File
http://j-walk.com/ss/excel/tips/tip82.htm

In your first post you were using defined names, in this post, absolute
ranges. You may have to play around to get what you want. For example for
defined ranges the following returns the value in 'rng' for a closed
workbook:

MsgBox ?ExecuteExcel4Macro("'D:\Data\[Book1.xls]Sheet1'!rng")

where 'rng' is a single-cell defined name. I don't know yet if there is a
way to get the top-leftmost cell in a multi-cell defined range.

Tim


"Gord D" wrote in message
...
Thanks for your suggestions guys. I gave them a shot, both with the
other sheet opened, and closed. (Ultimately, it has to work with the
other sheet closed)

I figured that if the workbook is closed, I'd have to get at it via the
full path (otherwise how would excel know where to look?)

At first glance, it doesn't look like you can get at ranges of closed
workbooks.

As you can see, I tried putting the full path, sheet, and range all
inside range(), and also tried specifying them in
Workbooks().worksheets().range()




'// doesn't work: Method 'Range' of Object '_Global'
failed

MsgBox " contents of cell in external range: " &
Range("\\hqserver01\rfp_accounti\TABLES\2003\[SCDCM.xls]SC_EL2_CM!A1:P60
00").Cells(1, 3).Value


'// works when SCDCM.xls is open, otherwise same as above
MsgBox " contents of cell in external range: " &
Range("[SCDCM.xls]SC_EL2_CM!A1:P6000").Cells(1, 3).Value




'// doesn't work: Subscript out of range

MsgBox " contents of cell in external range: " &
Workbooks("\\hqserver01\rfp_accounti\TABLES\2003\S CDCM.xls").Worksheets(
"SC_EL2_CM").Range("A1:P6000").Cells(1, 1).Value


'// works when SCDCM.xls is open, otherwise same as above
MsgBox " contents of cell in external range: " &
Workbooks("SCDCM.xls").Worksheets("SC_EL2_CM").Ran ge("A1:P6000").Cells(1
, 1).Value



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!