I have found the PULL code for updating links in closed files. However, does
anyone know what I need to do with this code? I pasted it in the code area
of the sheet I am working on...but now what? What modifications do I need to
do? Any help would be great!

Thanks in advance.

deeds wrote...
I have found the PULL code for updating links in closed files.
However, does anyone know what I need to do with this code? I
pasted it in the code area of the sheet I am working on...but
now what? What modifications do I need to do? Any help would
be great!

It's a udf, so you need to put the code into a general module, NOT a
worksheet's class module. If you've never used udfs, read through the
following page.

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

Once you have the code in a general module, you should be able to use
it in worksheet formulas. No modifications are needed.

Thanks Harlan....I did what you said....I also went to the link you provided
to read up...however, I still can't seem to get it to work. Do I insert
function (Pull)? What do I need to do to have my Indirect formula work when
the file is closed? Any help would be great! Thanks again!

"Harlan Grove" wrote:

deeds wrote...
I have found the PULL code for updating links in closed files.
However, does anyone know what I need to do with this code? I
pasted it in the code area of the sheet I am working on...but
now what? What modifications do I need to do? Any help would
be great!

It's a udf, so you need to put the code into a general module, NOT a
worksheet's class module. If you've never used udfs, read through the
following page.

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

Once you have the code in a general module, you should be able to use
it in worksheet formulas. No modifications are needed.

deeds wrote...
Thanks Harlan....I did what you said....I also went to the link you
provided to read up...however, I still can't seem to get it to work.
Do I insert function (Pull)? What do I need to do to have my
Indirect formula work when the file is closed? Any help would be
great! Thanks again!

You'd use pull INSTEAD OF INDIRECT.

If I

- create a new workbook in Excel,
- press [Alt]+[F11] to display the VB Editor (VBE),
- run the VBE menu command Insert Module to create a GENERAL module
in the workbook,
- paste my latest pull code into that module,
- press [Alt]+[F11] again to return to Excel,
- and enter a formula like

=pull("'"&"D:\test\"&"["&"foo.xls"&"]"&"Sheet1"&"'!"&"C5")

it returns the same value as the static external reference

='D:\test\[foo.xls]Sheet1'!C5

If you don't get similar results, provide DETAILS on what steps you're
taking to put the pull code into your workbook.

FYI, there's also Laurent Longre's MOREFUNC.XLL add-in, available from
http://xcell05.free.fr/english/, which provides an add-in function
named INDIRECT.EXT which provides pretty much the same functionality
as pull. It may be easier to get MOREFUNC to work.

Thanks again...worded great! However, what I ultimately like to do is use
this somehow in conjunction with a vlookup. I need to do a vlookup and have
it return the data when file is closed. Any ideas? Thanks.

"Harlan Grove" wrote:

deeds wrote...
Thanks Harlan....I did what you said....I also went to the link you
provided to read up...however, I still can't seem to get it to work.
Do I insert function (Pull)? What do I need to do to have my
Indirect formula work when the file is closed? Any help would be
great! Thanks again!

You'd use pull INSTEAD OF INDIRECT.

If I

- create a new workbook in Excel,
- press [Alt]+[F11] to display the VB Editor (VBE),
- run the VBE menu command Insert Module to create a GENERAL module
in the workbook,
- paste my latest pull code into that module,
- press [Alt]+[F11] again to return to Excel,
- and enter a formula like

=pull("'"&"D:\test\"&"["&"foo.xls"&"]"&"Sheet1"&"'!"&"C5")

it returns the same value as the static external reference

='D:\test\[foo.xls]Sheet1'!C5

If you don't get similar results, provide DETAILS on what steps you're
taking to put the pull code into your workbook.

FYI, there's also Laurent Longre's MOREFUNC.XLL add-in, available from
http://xcell05.free.fr/english/, which provides an add-in function
named INDIRECT.EXT which provides pretty much the same functionality
as pull. It may be easier to get MOREFUNC to work.

deeds wrote...
Thanks again...worded great! However, what I ultimately like to do
is use this somehow in conjunction with a vlookup. I need to do a
vlookup and have it return the data when file is closed. Any ideas?
....

First, use the latest version of the pull code, which is at

ftp://members.aol.com/hrlngrv/pull.zip

With that, if I define the following names,

mfpn d:\foo\[deleteme.xls]
wsn A
rn $B$2:$C$13

and the closed file 'd:\foo\[deleteme.xls]A'!$B$2:$C$13 contains

a 1
b 20
c 300
d 4,000
e 50,000
f 600,000
g 7,000,000
h 80,000,000
i 900,000,000
j 10,000,000,000
k 200,000,000,000
l 3,000,000,000,000

then if in another workbook A1 contains c and B1 contains the formula

=VLOOKUP(A1,pull("'"&mfpn&wsn&"'!"&rn),2)

the formula returns 300, as expected.

You use pull like INDIRECT. Once you've entered its code into a
general module, you can just start using it in worksheet formulas.