Harlan Grove PULL Code Help Please

[Flemming Christensen]

Hello,
I am strugling to understand what Harlon Grove's PULL function offers over and above what Excel 2003 already offers when you open a workbook containing links to other data sources and then press update.
Regards
Flemming

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.

On Tuesday, March 13, 2007 12:57 PM Harlan Grove wrote:

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.

On Wednesday, March 14, 2007 12:01 PM Harlan Grove wrote:

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.

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.

.now I can't seem to get the formula to work...it is
giving me a "The formula contains an error..."message. I setup your exact
example...here is my formula:
=VLOOKUP(A1,pull("'"&c:\foo\[deleteme.xls]&A&"'!"&$B$2:$C$8),2)

What is the problem with this?? Thanks again.




"Harlan Grove" wrote:

You need to use double quotes around "c:\foo\[deleteme.xls]", "A" and
"$B$2:$C$8", so the formula needs to be

=VLOOKUP(A1,pull("'"&"c:\foo\[deleteme.xls]"&"A"&"'!"&"$B$2:$C$8"),2)

In your formula above, the c:\foo\[deleteme.xls] would be a syntax
error, A would have been interpreted as a defined names in the calling
workbook (the workbook containing the formula containing the pull
call), and $B$2:$C$8 would have been evaluated as an array of the
values in the same worksheet in the calling workbook.

pull's one & only argument is a STRING. It may be easier to construct
its string argument in a different cell with a leading equal sign,
e.g.,

X99:
="='"&"c:\foo\[deleteme.xls]"&"A"&"'!"&"$B$2:$C$8"

which should evaluate to

='c:\foo\[deleteme.xls]A'!$B$2:$C$8

Copy that cell and paste special as value into another cell, then in
that other cell press [F2] then [Enter] or for multiple cell ranges
[Shift]+[Ctrl]+[Enter]. That'll enter the string as a formula. If THAT
doesn't evaluate correctly, there's a problem in your reference that
has nothing to do with pull.

If this constructed literal external reference works, then use it in
the pull call, e.g.,

=VLOOKUP(A1,pull(MID(X99,2,255)),2)

where the MID call skips over the leading = in the X99 formula. In
other words, if you get the pasted-as-value result of the X99 formula
to evaluate correctly as a literal external reference, pull should
evaluate correctly using MID(X99,2,255) as its argument.

On Thursday, March 15, 2007 2:26 PM Harlan Grove wrote:

With c:\foo\deleteme.xls open, the following works.

=COUNTIF([deleteme.xls]A!$C$2:$C$13,"<1E6") returns 6

But with c:\foo\deleteme.xls closed, this formula fails and becomes

=COUNTIF('C:\foo\[deleteme.xls]A'!$C$2:$C$13,"<1E6") returns #VALUE!

The equivalent SUMPRODUCT formula

=SUMPRODUCT(--('C:\foo\[deleteme.xls]A'!$C$2:$C$13<1E6))

returns 6 whether c:\foo\deleteme.xls is open or closed. Replace the
literal external reference with an equivalent pull call.

=SUMPRODUCT(--(pull("'"&"C:\foo\"&"["&"deleteme.xls"&"]"&"A"&"'!"
&"$C$2:$C$13")<1E6))

.One more minor thing....in these formulas...what would I
need to change to have the [filename] only in another cell...which is
referenced in the pull formula. In otherwords....I want the pull formula to
go look to another cell for the filename (of the closed workbook)...so
something like +Sheet1!A1 which in this cell is the filename (only) not the
path and sheet name etc...

Thanks much!

"Harlan Grove" wrote:

 

[Pete_UK]

If you use the INDIRECT function to try to get data from an external
workbook which is closed then it will not work and will return an
error. It will work if the external workbook is open.

However, it is not always convenient to have that external workbook
open, as it will take up memory and may slow other processes down, and
besides there might be several external workbooks that the data is
needed from. The PULL function can be used in this case. As Harlan
mentions in one of his replies, there is a free add-in called morefunc
which has a similar function INDIRECT.EXT, which can be used in a
similar way.

Hope this helps.

Pete

Hello,
I am strugling to understand what Harlon Grove's PULL function offers over and above what Excel 2003 already offers when you open a workbook containing links to other data sources and then press update.
Regards
Flemming

On Tuesday, March 13, 2007 9:19 AM deed 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 Ineed to
do?  Any help would be great!

Thanks in advance.

On Tuesday, March 13, 2007 12:57 PM Harlan Grove wrote:
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.
.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!
:
On Wednesday, March 14, 2007 12:01 PM Harlan Grove wrote:
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.
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.
:
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.
Attribute VB_Name = "pull"
Option Explicit
It is stopping on this and bringing me to the code....any idea what I am
doing wrong?  Thanks again!
:
Delete the Attribute line. It's generated when exporting from the VBE,
and when imported into the VBE it'd name the new module. But you're
pasting code, so it serves no purpose other than to throw syntax
errors.
.now I can't seem to get the formula to work...it is
giving me a "The formula contains an error..."message.  I setup your exact
example...here is my formula:
=VLOOKUP(A1,pull("'"&c:\foo\[deleteme.xls]&A&"'!"&$B$2:$C$8),2)
What is the problem with this??  Thanks again.
:
.now I got passed the formula error (missing single quotes)...now the
result is "#Value!".....any ideas why?  Thanks.
:
You need to use double quotes around "c:\foo\[deleteme.xls]","A" and
"$B$2:$C$8", so the formula needs to be
=VLOOKUP(A1,pull("'"&"c:\foo\[deleteme.xls]"&"A"&"'!"&"$B$2:$C$8"),2)
In your formula above, the c:\foo\[deleteme.xls] would be a syntax
error, A would have been interpreted as a defined names in the calling
workbook (the workbook containing the formula containing the pull
call), and $B$2:$C$8 would have been evaluated as an array ofthe
values in the same worksheet in the calling workbook.
pull's one & only argument is a STRING. It may be easier to construct
its string argument in a different cell with a leading equal sign,
e.g.,
X99:
="='"&"c:\foo\[deleteme.xls]"&"A"&"'!"&"$B$2:$C$8"
which should evaluate to
='c:\foo\[deleteme.xls]A'!$B$2:$C$8
Copy that cell and paste special as value into another cell, then in
that other cell press [F2] then [Enter] or for multiple cell ranges
[Shift]+[Ctrl]+[Enter]. That'll enter the string as a formula.. If THAT
doesn't evaluate correctly, there's a problem in your reference that
has nothing to do with pull.
If this constructed literal external reference works, then use it in
the pull call, e.g.,
=VLOOKUP(A1,pull(MID(X99,2,255)),2)
where the MID call skips over the leading = in the X99 formula. In
other words, if you get the pasted-as-value result of the X99formula
to evaluate correctly as a literal external reference, pull should
evaluate correctly using MID(X99,2,255) as its argument.
.Truly outstanding!...Nice work!  Thanks for everything!
:
.now...I have other formulas: one with Countif and
one with Sumproduct....will these work with the pull methodtoo?  I am trying
the countif formula...but so far coming up with #Value!  Any thoughts?  
Thanks!
:
SUMPRODUCT should work, but COUNTIF won't. COUNTIF (and SUMIF) *only*
accept range references as their 1st (and SUMIF's optional3rd)
arguments, and Excel only evaluates references to ranges in OPEN
workbooks as range references. It evaluates references to ranges in
closed workbooks are arrays. pull does no differently.
You need to change your COUNTIF formulas to equivalent SUMPRODUCT
formulas.
if you wouldn't mind, could you throw me a standard sumproduct
formula using pull so I can see how it should work...if you can use your
previous example that would be great...thanks.
:
\foo\deleteme.xls open, the following works.
=COUNTIF([deleteme.xls]A!$C$2:$C$13,"<1E6")  returns6
But with c:\foo\deleteme.xls closed, this formula fails and becomes
=COUNTIF('C:\foo\[deleteme.xls]A'!$C$2:$C$13,"<1E6")  returns #VALUE!
The equivalent SUMPRODUCT formula
=SUMPRODUCT(--('C:\foo\[deleteme.xls]A'!$C$2:$C$13<1E6))
returns 6 whether c:\foo\deleteme.xls is open or closed.Replace the
literal external

...

read more »- Hide quoted text -

- Show quoted text -