INDIRECT.EXT function, PULL function

P

paul

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
 
B

Bob Phillips

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)
 
F

Frank Kabel

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)
 
H

Harlan Grove

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.

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

Peo Sjoblom

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 said:
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.

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

Paul

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
-----Original Message-----
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.

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

Harlan Grove

Paul said:
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 **

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

Peo Sjoblom

Harlan Grove said:
Paul said:
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..

--


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

Regards,

Peo Sjoblom
 
P

paul

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 said:
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 **

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


.
 
M

markb

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
 
D

Dave Peterson

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



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
 
P

paul.moles

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top