Hey Jim,
You won't be able to use the entire path unless you use the INDIRECT.EXT function - which I believe is in the morefunc.xll add-in. And even that has it's limitations. You can reference the workbook (no path) with the INDIRECT. Also, even then, INDIRECT cannot handle 3-D array's. It's a pain, and MS should change it, as it's been mentioned quite a many times, but they haven't.
There are some workarounds that you can do though.
Method 1:
This UDF written by Harlan Grove (Pull) will open a seperate instance of a workbook in which to extract data from.
<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Function</SPAN> Pull(xref <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#007F00">'inspired by Bob Phillips and Laurent Longre</SPAN>
<SPAN style="color:#007F00">'but written by Harlan Grove</SPAN>
<SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">'Copyright (c) 2003 Harlan Grove.</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'This code is free software; you can redistribute it and/or modify</SPAN>
<SPAN style="color:#007F00">'it under the terms of the GNU General Public License as published</SPAN>
<SPAN style="color:#007F00">'by the Free Software Foundation; either version 2 of the License,</SPAN>
<SPAN style="color:#007F00">'or (at your option) any later version.</SPAN>
<SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> xlapp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, xlwb <SPAN style="color:#00007F">As</SPAN> Workbook
<SPAN style="color:#00007F">Dim</SPAN> b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, r <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
Pull = Evaluate(xref)
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">CStr</SPAN>(Pull) = <SPAN style="color:#00007F">CStr</SPAN>(CVErr(xlErrRef)) <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> CleanUp <SPAN style="color:#007F00">'immediate clean-up at this point</SPAN>
<SPAN style="color:#00007F">Set</SPAN> xlapp = CreateObject("Excel.Application")
<SPAN style="color:#00007F">Set</SPAN> xlwb = xlapp.Workbooks.Add <SPAN style="color:#007F00">'needed by .ExecuteExcel4Macro</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN> <SPAN style="color:#007F00">'now clean-up can wait</SPAN>
n = InStr(<SPAN style="color:#00007F">In</SPAN>Str(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)
<SPAN style="color:#00007F">Set</SPAN> r = xlwb.Sheets(1).Range(Mid(xref, n + 1))
<SPAN style="color:#00007F">If</SPAN> r <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
Pull = xlapp.ExecuteExcel4Macro(xref)
<SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
<SPAN style="color:#00007F">Next</SPAN> c
Pull = r.Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
CleanUp:
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> xlwb <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> xlwb.Close 0
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> xlapp <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> xlapp.Quit
<SPAN style="color:#00007F">Set</SPAN> xlapp = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>
An explanation of it's use can be found
here.
J-Walk also has a GetValue method for extracting data from closed workbooks
found here.
Method 2:
Download/install the morefunc.xll add-in and make use of the INDIRECT.EXT function. †
Add-in
found here.
Method 3:
Use an SQL.REQUEST, of which I am unfamiliar, but is laid out
here. Instructions provided at site
and with add-in.
Method 4:
Use an INDEX/MATCH combo (or othe suitable function desired) and type your formula in like this ...
Code:
="=INDEX('C:\Documents and Settings\Rob\Desktop\[12346 Jones.xls]Sheet1'!D9:E1000,match(r3,'C:\Documents and Settings\Rob\Desktop\[12346 Jones.xls]Sheet1'!E9:E1000),1)"
Note that I have changed the path for testing purposes. Copy the cell with the formula in it, then paste special --> values, making the value/formula static.
Next do a Find/Replace on that cell. Find = and Replace with = . (Sounds redundant, but it works.) The bad side to this is it takes up a lot of memory.
† Doesn't work with Defined Names of closed workbooks; and apparently does not work on all systems. Test first.