INDIRECT.EXT function, PULL function
paul
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
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
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
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
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
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
>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
> "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
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
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
='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
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
Thank you for your amazing work with Pull function (attached)!!
It works very well.
My problem is that it use:
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
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
pull = r.Value
End If
Thanks & regards,
Grant