INDIRECT.EXT function, PULL function

13144 views
Skip to first unread message

paul

unread,
May 27, 2004, 11:32:35 PM5/27/04
to
INDIRECT.EXT successfully returns the VALUE (CONTENTS) in
the target cell from an open or closed workbook and works
for SUM() etc., but not an address range for use in say
VLOOKUP() or MATCH() search ranges.
PULL returns a range address but does not work when the
target workbook is OPEN.
I need a solution to obtain a range address from a
workbook (both open or closed) to use in the MATCH or
VLOOKUP functions.
Can anyone help,
Thanks in anticipation

Paul

Bob Phillips

unread,
May 28, 2004, 3:10:34 AM5/28/04
to
Write yourself a simple UDF to check if the workbook is open, and then use
something like

=IF(WbIsOPen(Wkbk_name), INDIRECT(...), PULL(...))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"paul" <anon...@discussions.microsoft.com> wrote in message
news:1410a01c44464$6acc5190$a101...@phx.gbl...

Frank Kabel

unread,
May 28, 2004, 3:11:23 AM5/28/04
to
Hi Paul
not able to test this (as I don't have 'Pull' on my work
computer) but if I remebered correctly Harlan's Pull
function works also for open workbooks?

But as a kludgy workaround you may use a formula like the
following:
=IF(ISERROR(function_with_INDIRECT),
function_with_PULL,function_with_INDIRECT)

>.
>

Harlan Grove

unread,
May 28, 2004, 1:34:42 PM5/28/04
to
"paul" wrote...

If I hadn't screwed up the 'fix' I made to the pull function at the end of
March, it would work with open as well as closed files. It should work with
both, so here's another fix, rather more thoroughly tested than the last version
I spewed forth.


'----- begin VBA -----
Function pull(xref As String) As Variant
'inspired by Bob Phillips and Laurent Longre
'but written by Harlan Grove
'-----------------------------------------------------------------
'Copyright (c) 2003 Harlan Grove.
'
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------
'2004-05-28
'fixed the previous fix - replaced all instances of 'expr' with 'xref'
'also now checking for initial single quote in xref, and if found
'advancing past it to get the full pathname [dumb, really dumb!]
'-----------------------------------------------------------------
'2004-03-25
'revised to check if filename in xref exists - if it does, proceed;
'otherwise, return a #REF! error immediately - this avoids Excel
'displaying dialogs when the referenced file doesn't exist
'-----------------------------------------------------------------

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long

'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **
n = InStrRev(Len(xref), xref, "\")

If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)

Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)

End If

'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)

On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0

End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **

pull = Evaluate(xref)

If CStr(pull) = CStr(CVErr(xlErrRef)) Then
On Error GoTo CleanUp 'immediate clean-up at this point

Set xlapp = CreateObject("Excel.Application")
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro

On Error Resume Next 'now clean-up can wait

n = InStr(InStr(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)

Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C

pull = r.Value

End If

CleanUp:
If Not xlwb Is Nothing Then xlwb.Close 0
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End If

End Function
'----- end VBA -----

--
To top-post is human, to bottom-post and snip is sublime.

Peo Sjoblom

unread,
May 28, 2004, 2:17:23 PM5/28/04
to
Thanks Harlan, I used the old Pull function as an add in, time to change and
test this one

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom


"Harlan Grove" <hrl...@aol.com> wrote in message
news:SKKtc.4537$H4....@www.newsranger.com...

Paul

unread,
May 30, 2004, 3:10:37 PM5/30/04
to
The following VLOOKUP returns #value for both open and
closed workbooks when using the pull function posted below

VLOOKUP($A$3,Personal.xls!pull($C$3),5,FALSE)
where C3 contains
'Q:\NHL\[After Purge Accounts List 040518.xls]ImportData'!
$A$1:$H$50

It works successfully when using the old pull function
from closed workbooks but returns #value when the target
book is open.
Any ideas?
Paul

>.
>

Harlan Grove

unread,
May 31, 2004, 3:09:20 AM5/31/04
to
"Paul" <anon...@discussions.microsoft.com> wrote...

>The following VLOOKUP returns #value for both open and
>closed workbooks when using the pull function posted below
>
>VLOOKUP($A$3,Personal.xls!pull($C$3),5,FALSE)
>where C3 contains
>'Q:\NHL\[After Purge Accounts List 040518.xls]ImportData'!
>$A$1:$H$50
>
>It works successfully when using the old pull function
>from closed workbooks but returns #value when the target
>book is open.
>Any ideas?
...

Yes. I should test in both XL8/97 and XL2K before posting. I had written and
tested pull under XL8/97, and it seems XL2K and later may handle some things
differently. See the comments for details. This version works with the
sample workbooks you sent me.

'----- begin VBA -----
Function pull(xref As String) As Variant
'inspired by Bob Phillips and Laurent Longre
'but written by Harlan Grove
'-----------------------------------------------------------------
'Copyright (c) 2003 Harlan Grove.
'
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------

'2004-05-30
'still more fixes, this time to address apparent differences between
'XL8/97 and later versions. Specifically, fixed the InStrRev call,
'which is fubar in later versions and was using my own hacked version
'under XL8/97 which was using the wrong argument syntax. Also either
'XL8/97 didn't choke on CStr(pull) called when pull referred to an
'array while later versions do, or I never tested the 2004-03-25 fix
'against multiple cell references.
'-----------------------------------------------------------------


'2004-05-28
'fixed the previous fix - replaced all instances of 'expr' with 'xref'
'also now checking for initial single quote in xref, and if found
'advancing past it to get the full pathname [dumb, really dumb!]
'-----------------------------------------------------------------
'2004-03-25
'revised to check if filename in xref exists - if it does, proceed;
'otherwise, return a #REF! error immediately - this avoids Excel
'displaying dialogs when the referenced file doesn't exist
'-----------------------------------------------------------------

Dim xlapp As Object, xlwb As Workbook
Dim b As String, r As Range, C As Range, n As Long

'** begin 2004-05-30 changes **


'** begin 2004-05-28 changes **
'** begin 2004-03-25 changes **

n = InStrRev(xref, "\")

If n > 0 Then
If Mid(xref, n, 2) = "\[" Then
b = Left(xref, n)
n = InStr(n + 2, xref, "]") - n - 2
If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)

Else
n = InStrRev(Len(xref), xref, "!")
If n > 0 Then b = Left(xref, n - 1)

End If

'** key 2004-05-28 addition **
If Left(b, 1) = "'" Then b = Mid(b, 2)

On Error Resume Next
If n > 0 Then If Dir(b) = "" Then n = 0
Err.Clear
On Error GoTo 0

End If

If n <= 0 Then
pull = CVErr(xlErrRef)
Exit Function
End If
'** end 2004-03-25 changes **
'** end 2004-05-28 changes **

pull = Evaluate(xref)

'** key 2004-05-30 addition **
If IsArray(pull) Then Exit Function
'** end 2004-05-30 changes **

Peo Sjoblom

unread,
May 31, 2004, 10:42:25 AM5/31/04
to

"Harlan Grove" <hrl...@aol.com> wrote in message
news:ASAuc.4700$H4...@www.newsranger.com...

> "Paul" <anon...@discussions.microsoft.com> wrote...
> >The following VLOOKUP returns #value for both open and
> >closed workbooks when using the pull function posted below
> >
> >VLOOKUP($A$3,Personal.xls!pull($C$3),5,FALSE)
> >where C3 contains
> >'Q:\NHL\[After Purge Accounts List 040518.xls]ImportData'!
> >$A$1:$H$50
> >
> >It works successfully when using the old pull function
> >from closed workbooks but returns #value when the target
> >book is open.
> >Any ideas?
> ...
>
> Yes. I should test in both XL8/97 and XL2K before posting. I had written
and
> tested pull under XL8/97, and it seems XL2K and later may handle some
things
> differently. See the comments for details. This version works with the
> sample workbooks you sent me.

Just tested this version in Excel 2003 and it works fine..

paul

unread,
Jun 3, 2004, 4:28:38 AM6/3/04
to
Thanks for the assistance. Revised function working
well. My range sizes just now need to be reduced to
speed up calculation.

Cheers
Paul


>-----Original Message-----
>"Paul" <anon...@discussions.microsoft.com> wrote...
>>The following VLOOKUP returns #value for both open and
>>closed workbooks when using the pull function posted
below
>>
>>VLOOKUP($A$3,Personal.xls!pull($C$3),5,FALSE)
>>where C3 contains
>>'Q:\NHL\[After Purge Accounts List 040518.xls]
ImportData'!
>>$A$1:$H$50
>>
>>It works successfully when using the old pull function
>>from closed workbooks but returns #value when the target
>>book is open.
>>Any ideas?

>....

>.
>

ma...@insideandout.com

unread,
Jan 25, 2005, 11:39:31 AM1/25/05
to
I need some advice on how to pull data from a closed worksheet to a
master doc. I am using a cell function like below.

=CELL("contents",'P:\Blanco Kitchen - 200447\[200447-Process
Map.xls]Actions'!$C$4:$D$4)

It works fine if the Blanco Kitchen doc is open, but I have over twenty
projects to tract simultaneously and it is a hassle to open all of them
to update the master doc.

Any help would be appreciated.
Regards,

Mark Benzell
Inside & Out Remodelers

Dave Peterson

unread,
Jan 25, 2005, 10:17:57 PM1/25/05
to
how about dropping the =cell("contents",xxx) stuff and just:

='P:\Blanco Kitchen - 200447\[200447-Process Map.xls]Actions'!$C$4

(not quite sure why you were using two cells (c4:d4), though.)

--

Dave Peterson

paul....@gmail.com

unread,
Jan 30, 2015, 10:50:54 AM1/30/15
to
Correct Syntax for use of pull

I have a spreadsheet in which I am trying to use Harlan Groves UDF to collect data from over 100 closed workbooks.
I have installed the UDF correctly I believe, as it shows as a user defined function.

Directory path is stored as a hyperlink in B1,
Filenames listed as hyperlinks from A4 to A100 (and growing)
Sheet Name is constant "Ingredient detail" but I have also tried putting this in F1
Cell references are constant E10

The file listing is updated by autorun macro (I found but cannot credit) each time the workbook is opened as more files may have been added to the directory since last opening. It creates an alphabetically sorted list of hyperlinked filenames.

I have gotten as far as
=pull("'"&B1&"\"&"["&A4&"]"&"Ingredient detail"&"'!"&"E10")
also
=pull("'"&B1&"\"&"["&A4&"]"&F1&"'!"&"E10")

each returns #VALUE! error

Where have I gone wrong?

Thanks in advance any assistance appreciated.

Paul



evgue...@gmail.com

unread,
Jan 18, 2018, 8:58:54 AM1/18/18
to
Dear Harlan Grove,
Thank you for your amazing work with Pull function (attached)!!

It works very well.
My problem is that it use:
Set xlapp = CreateObject("Excel.Application")

which, from what I understood (I am a beginner), creates each time a new Excel instance which weighs enormously the use of the function. This is a real problem for me because my table easily contains 1000 calls of the Pull() function.

However, a person by the name of Pappu Pager who wrote you gave a track to speed up the processing done by your pull function, here is the quote in question:
“…
This function is slow for only one reason. The Set xlapp = CreateObject(“Excel.Application”) has been defined in the function, which creates a new object every time a cell is changed.

We need to initialize it only once. So you can just declare – – public xlapp as object – – in the module just above the line – – Function pull(xref As String) As Variant
And
PUT THE – – Set xlapp = CreateObject(“Excel.Application”) within workbook_open event under THISWORKBOOK. Believe me it works awesome. I have tried it and pulled 100 rows each from 20 files in 15 seconds.

I can send you my excel files which are working with awesome speed. Please give me your email id.

Pappu Pager.
(From India that is Bharat)
…”

Did he sent you his files?
If yes, can you PLEASE send them to me too?

If not, and as I do not understand what he explains and I do not know how to put it into practice, I turn to you to know if you would have the extreme kindness of:
- Either modify your code to put into practice the modifications of Pappu Pager so that the macro is faster,
- Or explain me very concretely how to make these modifications so that I can do them myself.

Thank you in advance for any help you can bring me 

Best regards,
Prince

grant.s...@gmail.com

unread,
Dec 18, 2019, 1:02:59 AM12/18/19
to
Hi,

Appreciate this is a dormant thread - but just in case someone is monitoring.

I have tried to implement the pull function as per Harlan's most recent update, but notice that while it works for named ranges consisting of a single cell, it fails for multi-cell ranges.

It looks as if r below is returned as NOTHING regardless of whether the range is single or multi-celled. So the first limb of the if statement is executed regardless.

In the case where the range is a single-cell, the ExecuteExcel4Macro returns the value. But in the case of a multi-cell range, it simply falls over. My guess is that it should get to the second limb of the if statement in this case.

Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

If r Is Nothing Then
pull = xlapp.ExecuteExcel4Macro(xref)

Else
For Each C In r
C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1))
Next C

pull = r.Value

End If

Any thoughts or suggestions greatl appreciated.

Thanks & regards,

Grant
Reply all
Reply to author
Forward
0 new messages