# Microsoft Office Application Help - Excel Help forum > Excel General >  >  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

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?
>
> Thanks!

--

Dave Peterson

----------


## 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/micro...443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).

----------


## vane0326

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





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" <vane0326.24scrn_1142551501.2859@excelforum-nospam.com> wrote in
message news:vane0326.24scrn_1142551501.2859@excelforum-nospam.com...
>
> 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?
>>
>> Thanks!
>
>
>
> Let me know if you still need help because i may have a solution.
>
>
> --
> vane0326
> ------------------------------------------------------------------------
> vane0326's Profile:
> http://www.excelforum.com/member.php...o&userid=14731
> View this thread: http://www.excelforum.com/showthread...hreadid=522861
>

----------


## vane0326

Here is a link I recently posted.


http://www.excelforum.com/showthread...33#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.

"vane0326" <vane0326.24sn6m_1142565000.3568@excelforum-nospam.com> wrote in
message news:vane0326.24sn6m_1142565000.3568@excelforum-nospam.com...
>
> Here is a link I recently posted.
>
>
> http://www.excelforum.com/showthread...33#post1522233
>
>
> post back if you have any questions.
>
>
> --
> vane0326
> ------------------------------------------------------------------------
> vane0326's Profile:
> http://www.excelforum.com/member.php...o&userid=14731
> View this thread: http://www.excelforum.com/showthread...hreadid=522861
>

----------


## 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




"Hall" <hall@garp.org> wrote in message
news:e5ad6NXSGHA.1948@TK2MSFTNGP09.phx.gbl...
>I have no idea how this is supposed to make an indirect work on closed
>files.
>
> "vane0326" <vane0326.24sn6m_1142565000.3568@excelforum-nospam.com> wrote
> in message news:vane0326.24sn6m_1142565000.3568@excelforum-nospam.com...
>>
>> Here is a link I recently posted.
>>
>>
>> http://www.excelforum.com/showthread...33#post1522233
>>
>>
>> post back if you have any questions.
>>
>>
>> --
>> vane0326
>> ------------------------------------------------------------------------
>> vane0326's Profile:
>> http://www.excelforum.com/member.php...o&userid=14731
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=522861
>>
>
>

----------


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

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1142569846.053385.20670@u72g2000cwu.googlegroups.com...
> 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.
>

----------


## boisgontier@hotmail.com

http://cjoint.com/?drvBQnGZDT

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

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

JB

----------


## sstern

Try to use INDEX function
You can define name for the table in the other file

----------

