Can indirect() work using closed external files??

[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!

 

[azidrane]

I know they must be open and I have not found a way to make them work
yet. Anyone?

 

[Dave Peterson]

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

 

[Harlan Grove]

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?

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 ).

 

[vane0326]

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!

Let me know if you still need help because i may have a solution

 

[Hall]

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.

 

[vane0326]

Here is a link I recently posted.


http://www.excelforum.com/showthread.php?p=1522233#post1522233


post back if you have any questions

 

[Hall]

I have no idea how this is supposed to make an indirect work on closed
files.

 

[Peo Sjoblom]

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

 

[Harlan Grove]

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.

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.

 

[Hall]

Thanks Harlan. That helps put this in context and gives me direction to
try.

 

[boisgontier]

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