Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
external range in VBA (user defined formula)
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
external range in VBA (user defined formula)
In your first post you were using defined names, in t his post, absolute ranges. Yes, the reason behind that is that I couldn't get at the external ranges defined (from closed books) in the names, so I started trying to get at them any way possible. It puzzles me that you can use these ranges in functions within formulas (such as VLookup), but you can't get at them in VBA at all. I don't *actually* just want the top left cell. That was pretty much just for test purposes to see if I could get at anything in the range at all. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help text for user defined formula | Excel Worksheet Functions | |||
User-defined range for graph | Excel Discussion (Misc queries) | |||
Passing a range to a user defined function | Excel Discussion (Misc queries) | |||
"User-defined type not defined" message in Excel | Excel Discussion (Misc queries) | |||
external range in VBA (user defined formula) | Excel Programming |