Can indirect() work using closed external files??

  • Thread starter Thread starter Hall
  • Start date Start date
H

Hall

Workbook A has a cell that gets data from workbook B as an external link.
It does this using the indirect function because it needs to concatenate the
path and filename from other cells.

Problem is that if workbook B is closed, the cell in workbook A shows #REF!.

So can indirect work using closed external files or must the external files
be opened?

Is there a clever way of making that work?

Thanks!
 
Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip
 
Hall wrote...
Workbook A has a cell that gets data from workbook B as an external link.
It does this using the indirect function because it needs to concatenate the
path and filename from other cells.

Problem is that if workbook B is closed, the cell in workbook A shows #REF!.

So can indirect work using closed external files or must the external files
be opened?

Is there a clever way of making that work?
Click to expand...

There is *NO* way to make INDIRECT read from closed workbooks. The
alternatives are given in the following archived posting.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).
 
Hall said:
Workbook A has a cell that gets data from workbook B as an externa
link.
It does this using the indirect function because it needs t
concatenate the
path and filename from other cells.

Problem is that if workbook B is closed, the cell in workbook A show
#REF!.

So can indirect work using closed external files or must the externa
files
be opened?

Is there a clever way of making that work?

Thanks!
Click to expand...



Let me know if you still need help because i may have a solution
 
Well sure, I'd love to take you up on your offer since I have no idea how to
use vba or the solutions I received so far.
 
I think it is easier if you download and install Harlan's PULL function,
here's how to install macros or UDFs

http://www.mvps.org/dmcritchie/excel/install.htm

if you look at the links you were given or if you search Google for the PULL
function there are examples, another way would be to install Morefunc from
here

http://xcell05.free.fr/

it has a function called INDIRECT.EXT

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
Hall wrote...
Well sure, I'd love to take you up on your offer since I have no idea how to
use vba or the solutions I received so far.
Click to expand...

To repeat, INDIRECT will *NEVER* work on closed files. vane...'s
approach is a kludge which opens referenced workbooks then closes them
after recalc. It uses VBA. My own pull udf uses VBA, Microsoft's
SQL.REQUEST udf uses the old XLM macro language. You're likely best off
downloading and installing Laurent Longre's MOREFUNC.XLL add-in, which
is freely available at http://xcell05.free.fr/english/. Follow its
instructions for installing it. Once you have done so, you can use it's
INDIRECT.EXT function which can reference ranges and some defined names
in closed workbooks.
 
http://cjoint.com/?drvBQnGZDT

Sub LitClasseurFermé()
ChampOuCopier = "C2:C3"
Chemin = ActiveWorkbook.Path & "\source"
Fichier = "stock.xls"
onglet = "Janvier"
ChampAlire = "B2:B3"
LitChamp ChampOuCopier, Chemin, Fichier, onglet, ChampAlire
End Sub

Sub LitChamp(ChampOuCopier, Chemin, Fichier, onglet, ChampAlire)
Range(ChampOuCopier).Formula = "='" & Chemin & "\[" & Fichier & "]"
& onglet & "'!" & ChampAlire
Range(ChampOuCopier).Value = Range(ChampOuCopier).Value
End Sub

JB
 
Back
Top