Concatinate a filename

You'd want to use the =indirect() worksheet function. But that doesn't work
with closed files.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/

CLR wrote:
>
> Hi All........
>
> I am trying to concatenate two cells together to form a filename in a
> link............no joy, .......all I get is "That filename is not
> valid"........
>
> =[clean(c6)&"_"&b6.xls]Sheet1!$A9
>
> C6 is a name, as Jones, Fred
> B6 is a string as R1938
>
> The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> exist........and of course works if I hard code the filename into the
> formula........
>
> =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> the link formula
>
> Any help would be appreciated........
>
> Vaya con Dios,
> Chuck, CABGx3

--

Dave Peterson

Thank you kind Sir............I went and got the PULL file and will try it
tomorrow........about to fall off my chair tonight...........

Thanks again loads.......

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42E98A10.FCEF2CE6@verizonXSPAM.net...
> You'd want to use the =indirect() worksheet function. But that doesn't
work
> with closed files.
>
> Harlan Grove wrote a UDF called PULL that will retrieve the value from a
closed
> workbook.
>
> You can find the function at Harlan's FTP site:
> ftp://members.aol.com/hrlngrv/
>
> CLR wrote:
> >
> > Hi All........
> >
> > I am trying to concatenate two cells together to form a filename in a
> > link............no joy, .......all I get is "That filename is not
> > valid"........
> >
> > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> >
> > C6 is a name, as Jones, Fred
> > B6 is a string as R1938
> >
> > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > exist........and of course works if I hard code the filename into the
> > formula........
> >
> > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not
in
> > the link formula
> >
> > Any help would be appreciated........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
>
> --
>
> Dave Peterson

Hi,

use

=INDIRECT("'["&CLEAN(C6)&"_"&B6&".xls]Sheet1'!$A9")

Regards

Govind.

CLR wrote:
> Hi All........
>
> I am trying to concatenate two cells together to form a filename in a
> link............no joy, .......all I get is "That filename is not
> valid"........
>
> =[clean(c6)&"_"&b6.xls]Sheet1!$A9
>
> C6 is a name, as Jones, Fred
> B6 is a string as R1938
>
> The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> exist........and of course works if I hard code the filename into the
> formula........
>
> =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> the link formula
>
> Any help would be appreciated........
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>

Hi All........

I am trying to concatenate two cells together to form a filename in a
link............no joy, .......all I get is "That filename is not
valid"........

=[clean(c6)&"_"&b6.xls]Sheet1!$A9

C6 is a name, as Jones, Fred
B6 is a string as R1938

The filename I 'm looking for is Jones,Fred_R1938.xls and it does
exist........and of course works if I hard code the filename into the
formula........

=clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
the link formula

Any help would be appreciated........

Vaya con Dios,
Chuck, CABGx3

Thanks Govind...........I appreciate your response.

Vaya con Dios,
Chuck, CABGx3



"Govind" <adgraj1@indiatimes.com> wrote in message
news:qjgGe.29$lz1.183@news.oracle.com...
> Hi,
>
> use
>
> =INDIRECT("'["&CLEAN(C6)&"_"&B6&".xls]Sheet1'!$A9")
>
> Regards
>
> Govind.
>
> CLR wrote:
> > Hi All........
> >
> > I am trying to concatenate two cells together to form a filename in a
> > link............no joy, .......all I get is "That filename is not
> > valid"........
> >
> > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> >
> > C6 is a name, as Jones, Fred
> > B6 is a string as R1938
> >
> > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > exist........and of course works if I hard code the filename into the
> > formula........
> >
> > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not
in
> > the link formula
> >
> > Any help would be appreciated........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >

I went after Harlan's UDF and got it but can't for the life of me figure out
how to use it.
I pasted it into a regular module and, all I can get "Sub or function not
defined" on this line..

n = InStrRev(Len(expr), expr, "\")

Anybody see what I'm doing wrong?

Vaya con Dios,
Chuck, CABGx3


"Dave Peterson" wrote:

> You'd want to use the =indirect() worksheet function. But that doesn't work
> with closed files.
>
> Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
> workbook.
>
> You can find the function at Harlan's FTP site:
> ftp://members.aol.com/hrlngrv/
>
> CLR wrote:
> >
> > Hi All........
> >
> > I am trying to concatenate two cells together to form a filename in a
> > link............no joy, .......all I get is "That filename is not
> > valid"........
> >
> > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> >
> > C6 is a name, as Jones, Fred
> > B6 is a string as R1938
> >
> > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > exist........and of course works if I hard code the filename into the
> > formula........
> >
> > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> > the link formula
> >
> > Any help would be appreciated........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
>
> --
>
> Dave Peterson
>

Instrrev was added in xl2k.

You could create your own InstrRev97 function and use that:

Function InStrRev97(mystr As Variant, mydelim As String) As Long
Dim i As Long
InStrRev97 = 0
For i = Len(mystr) To 1 Step -1
If Mid(mystr, i, 1) = mydelim Then
InStrRev97 = i
Exit Function
End If
Next i
End Function

(Just add this to the bottom of that general module.

so
n = InStrRev(Len(expr), expr, "\")
becomes
n = InStrRev97(expr, "\")



=====
There are some other functions that were added in xl2k (split for example). But
I took a cursory glance at Harlan's code and didn't see any others that would
cause you trouble.

(Post back when you see that I missed one!)

CLR wrote:
>
> I went after Harlan's UDF and got it but can't for the life of me figure out
> how to use it.
> I pasted it into a regular module and, all I can get "Sub or function not
> defined" on this line..
>
> n = InStrRev(Len(expr), expr, "\")
>
> Anybody see what I'm doing wrong?
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "Dave Peterson" wrote:
>
> > You'd want to use the =indirect() worksheet function. But that doesn't work
> > with closed files.
> >
> > Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
> > workbook.
> >
> > You can find the function at Harlan's FTP site:
> > ftp://members.aol.com/hrlngrv/
> >
> > CLR wrote:
> > >
> > > Hi All........
> > >
> > > I am trying to concatenate two cells together to form a filename in a
> > > link............no joy, .......all I get is "That filename is not
> > > valid"........
> > >
> > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > >
> > > C6 is a name, as Jones, Fred
> > > B6 is a string as R1938
> > >
> > > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > > exist........and of course works if I hard code the filename into the
> > > formula........
> > >
> > > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> > > the link formula
> > >
> > > Any help would be appreciated........
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thanks Dave.........it got past that line by following your instructions, but
now stops on

n = InStrRev(Len(xref), xref, "!")

I tried modifying it, but no joy.....I'm just shooting in the dark.

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote:

> Instrrev was added in xl2k.
>
> You could create your own InstrRev97 function and use that:
>
> Function InStrRev97(mystr As Variant, mydelim As String) As Long
> Dim i As Long
> InStrRev97 = 0
> For i = Len(mystr) To 1 Step -1
> If Mid(mystr, i, 1) = mydelim Then
> InStrRev97 = i
> Exit Function
> End If
> Next i
> End Function
>
> (Just add this to the bottom of that general module.
>
> so
> n = InStrRev(Len(expr), expr, "\")
> becomes
> n = InStrRev97(expr, "\")
>
>
>
> =====
> There are some other functions that were added in xl2k (split for example). But
> I took a cursory glance at Harlan's code and didn't see any others that would
> cause you trouble.
>
> (Post back when you see that I missed one!)
>
> CLR wrote:
> >
> > I went after Harlan's UDF and got it but can't for the life of me figure out
> > how to use it.
> > I pasted it into a regular module and, all I can get "Sub or function not
> > defined" on this line..
> >
> > n = InStrRev(Len(expr), expr, "\")
> >
> > Anybody see what I'm doing wrong?
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> > "Dave Peterson" wrote:
> >
> > > You'd want to use the =indirect() worksheet function. But that doesn't work
> > > with closed files.
> > >
> > > Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
> > > workbook.
> > >
> > > You can find the function at Harlan's FTP site:
> > > ftp://members.aol.com/hrlngrv/
> > >
> > > CLR wrote:
> > > >
> > > > Hi All........
> > > >
> > > > I am trying to concatenate two cells together to form a filename in a
> > > > link............no joy, .......all I get is "That filename is not
> > > > valid"........
> > > >
> > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > >
> > > > C6 is a name, as Jones, Fred
> > > > B6 is a string as R1938
> > > >
> > > > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > > > exist........and of course works if I hard code the filename into the
> > > > formula........
> > > >
> > > > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> > > > the link formula
> > > >
> > > > Any help would be appreciated........
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Hi Govind.......

I tried your INDIRECT formula and it worked ok, after I enclosed the CLEAN
portion as (CLEAN(c6)) in parenthesis, but as Dave eluded, only if the File
is open.........I suppose I could "open the file, obtain the data, and close
the file", but I would rather not have to do that unless absolutely
necessary.........

Any other ideas, please?

Vaya con Dios,
Chuck, CABGx3



"Govind" wrote:

> Hi,
>
> use
>
> =INDIRECT("'["&CLEAN(C6)&"_"&B6&".xls]Sheet1'!$A9")
>
> Regards
>
> Govind.
>
> CLR wrote:
> > Hi All........
> >
> > I am trying to concatenate two cells together to form a filename in a
> > link............no joy, .......all I get is "That filename is not
> > valid"........
> >
> > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> >
> > C6 is a name, as Jones, Fred
> > B6 is a string as R1938
> >
> > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > exist........and of course works if I hard code the filename into the
> > formula........
> >
> > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> > the link formula
> >
> > Any help would be appreciated........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
>

You'll have to do the same kind of thing.

n = InStrRev(Len(xref), xref, "!")
becomes
n = InStrRev97(expr, "!")




CLR wrote:
>
> Thanks Dave.........it got past that line by following your instructions, but
> now stops on
>
> n = InStrRev(Len(xref), xref, "!")
>
> I tried modifying it, but no joy.....I'm just shooting in the dark.
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "Dave Peterson" wrote:
>
> > Instrrev was added in xl2k.
> >
> > You could create your own InstrRev97 function and use that:
> >
> > Function InStrRev97(mystr As Variant, mydelim As String) As Long
> > Dim i As Long
> > InStrRev97 = 0
> > For i = Len(mystr) To 1 Step -1
> > If Mid(mystr, i, 1) = mydelim Then
> > InStrRev97 = i
> > Exit Function
> > End If
> > Next i
> > End Function
> >
> > (Just add this to the bottom of that general module.
> >
> > so
> > n = InStrRev(Len(expr), expr, "\")
> > becomes
> > n = InStrRev97(expr, "\")
> >
> >
> >
> > =====
> > There are some other functions that were added in xl2k (split for example). But
> > I took a cursory glance at Harlan's code and didn't see any others that would
> > cause you trouble.
> >
> > (Post back when you see that I missed one!)
> >
> > CLR wrote:
> > >
> > > I went after Harlan's UDF and got it but can't for the life of me figure out
> > > how to use it.
> > > I pasted it into a regular module and, all I can get "Sub or function not
> > > defined" on this line..
> > >
> > > n = InStrRev(Len(expr), expr, "\")
> > >
> > > Anybody see what I'm doing wrong?
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You'd want to use the =indirect() worksheet function. But that doesn't work
> > > > with closed files.
> > > >
> > > > Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
> > > > workbook.
> > > >
> > > > You can find the function at Harlan's FTP site:
> > > > ftp://members.aol.com/hrlngrv/
> > > >
> > > > CLR wrote:
> > > > >
> > > > > Hi All........
> > > > >
> > > > > I am trying to concatenate two cells together to form a filename in a
> > > > > link............no joy, .......all I get is "That filename is not
> > > > > valid"........
> > > > >
> > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > >
> > > > > C6 is a name, as Jones, Fred
> > > > > B6 is a string as R1938
> > > > >
> > > > > The filename I 'm looking for is Jones,Fred_R1938.xls and it does
> > > > > exist........and of course works if I hard code the filename into the
> > > > > formula........
> > > > >
> > > > > =clean(c6)&"_"&b6 works fine in a test of that standalone part, but not in
> > > > > the link formula
> > > > >
> > > > > Any help would be appreciated........
> > > > >
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Thanks Dave.........yeah, I finally did that but still no joy...........it
don't give error messages any more, but it also don't give
results........depending on what I type in the =PULL(), I usually get
#VALUE! or #REF!..........I've tried on both 97 and 2000.........I've even
got both May 05 updates and still cant seem to get it to work.......I guess
maybe it's time to start back at square one..............some days the
Dragon wins........

Vaya con Dios,
Chuck, CABGx3




"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42EAB61F.FE3FF849@verizonXSPAM.net...
> You'll have to do the same kind of thing.
>
> n = InStrRev(Len(xref), xref, "!")
> becomes
> n = InStrRev97(expr, "!")
>
>
>
>
> CLR wrote:
> >
> > Thanks Dave.........it got past that line by following your
instructions, but
> > now stops on
> >
> > n = InStrRev(Len(xref), xref, "!")
> >
> > I tried modifying it, but no joy.....I'm just shooting in the dark.
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> > "Dave Peterson" wrote:
> >
> > > Instrrev was added in xl2k.
> > >
> > > You could create your own InstrRev97 function and use that:
> > >
> > > Function InStrRev97(mystr As Variant, mydelim As String) As Long
> > > Dim i As Long
> > > InStrRev97 = 0
> > > For i = Len(mystr) To 1 Step -1
> > > If Mid(mystr, i, 1) = mydelim Then
> > > InStrRev97 = i
> > > Exit Function
> > > End If
> > > Next i
> > > End Function
> > >
> > > (Just add this to the bottom of that general module.
> > >
> > > so
> > > n = InStrRev(Len(expr), expr, "\")
> > > becomes
> > > n = InStrRev97(expr, "\")
> > >
> > >
> > >
> > > =====
> > > There are some other functions that were added in xl2k (split for
example). But
> > > I took a cursory glance at Harlan's code and didn't see any others
that would
> > > cause you trouble.
> > >
> > > (Post back when you see that I missed one!)
> > >
> > > CLR wrote:
> > > >
> > > > I went after Harlan's UDF and got it but can't for the life of me
figure out
> > > > how to use it.
> > > > I pasted it into a regular module and, all I can get "Sub or
function not
> > > > defined" on this line..
> > > >
> > > > n = InStrRev(Len(expr), expr, "\")
> > > >
> > > > Anybody see what I'm doing wrong?
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > You'd want to use the =indirect() worksheet function. But that
doesn't work
> > > > > with closed files.
> > > > >
> > > > > Harlan Grove wrote a UDF called PULL that will retrieve the value
from a closed
> > > > > workbook.
> > > > >
> > > > > You can find the function at Harlan's FTP site:
> > > > > ftp://members.aol.com/hrlngrv/
> > > > >
> > > > > CLR wrote:
> > > > > >
> > > > > > Hi All........
> > > > > >
> > > > > > I am trying to concatenate two cells together to form a filename
in a
> > > > > > link............no joy, .......all I get is "That filename is
not
> > > > > > valid"........
> > > > > >
> > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > >
> > > > > > C6 is a name, as Jones, Fred
> > > > > > B6 is a string as R1938
> > > > > >
> > > > > > The filename I 'm looking for is Jones,Fred_R1938.xls and it
does
> > > > > > exist........and of course works if I hard code the filename
into the
> > > > > > formula........
> > > > > >
> > > > > > =clean(c6)&"_"&b6 works fine in a test of that standalone part,
but not in
> > > > > > the link formula
> > > > > >
> > > > > > Any help would be appreciated........
> > > > > >
> > > > > > Vaya con Dios,
> > > > > > Chuck, CABGx3
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson

I was going to give you a google post:
http://groups.google.co.uk/group/mic...49f6c074a3adfd
(one line in your browser)

But google is adding extra characters in the code and screwing it up.

So I thought that a link to Harlan's FTP site would be better. But I just
looked at it and it's not up to date with what he's posted on the newsgroups.

So I used the (most???) current version that I saw on google and tried to clean
up those google induced errors.

I tested it to make sure it works with a call like:

=pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")

Here's Harlan's code (but you'll need to still make that instrrev97 change):

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


CLR wrote:
>
> Thanks Dave.........yeah, I finally did that but still no joy...........it
> don't give error messages any more, but it also don't give
> results........depending on what I type in the =PULL(), I usually get
> #VALUE! or #REF!..........I've tried on both 97 and 2000.........I've even
> got both May 05 updates and still cant seem to get it to work.......I guess
> maybe it's time to start back at square one..............some days the
> Dragon wins........
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > You'll have to do the same kind of thing.
> >
> > n = InStrRev(Len(xref), xref, "!")
> > becomes
> > n = InStrRev97(expr, "!")
> >
> >
> >
> >
> > CLR wrote:
> > >
> > > Thanks Dave.........it got past that line by following your
> instructions, but
> > > now stops on
> > >
> > > n = InStrRev(Len(xref), xref, "!")
> > >
> > > I tried modifying it, but no joy.....I'm just shooting in the dark.
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Instrrev was added in xl2k.
> > > >
> > > > You could create your own InstrRev97 function and use that:
> > > >
> > > > Function InStrRev97(mystr As Variant, mydelim As String) As Long
> > > > Dim i As Long
> > > > InStrRev97 = 0
> > > > For i = Len(mystr) To 1 Step -1
> > > > If Mid(mystr, i, 1) = mydelim Then
> > > > InStrRev97 = i
> > > > Exit Function
> > > > End If
> > > > Next i
> > > > End Function
> > > >
> > > > (Just add this to the bottom of that general module.
> > > >
> > > > so
> > > > n = InStrRev(Len(expr), expr, "\")
> > > > becomes
> > > > n = InStrRev97(expr, "\")
> > > >
> > > >
> > > >
> > > > =====
> > > > There are some other functions that were added in xl2k (split for
> example). But
> > > > I took a cursory glance at Harlan's code and didn't see any others
> that would
> > > > cause you trouble.
> > > >
> > > > (Post back when you see that I missed one!)
> > > >
> > > > CLR wrote:
> > > > >
> > > > > I went after Harlan's UDF and got it but can't for the life of me
> figure out
> > > > > how to use it.
> > > > > I pasted it into a regular module and, all I can get "Sub or
> function not
> > > > > defined" on this line..
> > > > >
> > > > > n = InStrRev(Len(expr), expr, "\")
> > > > >
> > > > > Anybody see what I'm doing wrong?
> > > > >
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > You'd want to use the =indirect() worksheet function. But that
> doesn't work
> > > > > > with closed files.
> > > > > >
> > > > > > Harlan Grove wrote a UDF called PULL that will retrieve the value
> from a closed
> > > > > > workbook.
> > > > > >
> > > > > > You can find the function at Harlan's FTP site:
> > > > > > ftp://members.aol.com/hrlngrv/
> > > > > >
> > > > > > CLR wrote:
> > > > > > >
> > > > > > > Hi All........
> > > > > > >
> > > > > > > I am trying to concatenate two cells together to form a filename
> in a
> > > > > > > link............no joy, .......all I get is "That filename is
> not
> > > > > > > valid"........
> > > > > > >
> > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > >
> > > > > > > C6 is a name, as Jones, Fred
> > > > > > > B6 is a string as R1938
> > > > > > >
> > > > > > > The filename I 'm looking for is Jones,Fred_R1938.xls and it
> does
> > > > > > > exist........and of course works if I hard code the filename
> into the
> > > > > > > formula........
> > > > > > >
> > > > > > > =clean(c6)&"_"&b6 works fine in a test of that standalone part,
> but not in
> > > > > > > the link formula
> > > > > > >
> > > > > > > Any help would be appreciated........
> > > > > > >
> > > > > > > Vaya con Dios,
> > > > > > > Chuck, CABGx3
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Thanks Dave.............I'll have a go at it tomorrow........I'm totally
burnt out tonight..........fresh start in the morning and it will probably
fall right in to place......just can't see the forrest for the trees
tonight..........

I do appreciate your time........this is actually quite important to me.
I'm starting a new project and this feature plays a major part.....I just
need to calm down and make it work. I'll have maybe 300 of these PULL's on
each of about 50 Training Matrix Workbooks........they will actually be
inside concatenated VLOOKUPs, and will draw from 500-600 individual
employee files......and the whole thing tied together with a few menu's and
a little VBA........fun for me, and I learn something new
everyday............

Many, many thanks again,
Vaya con Dios,
Chuck, CABGx3






"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42EAC719.67F023E3@verizonXSPAM.net...
> I was going to give you a google post:
>
http://groups.google.co.uk/group/mic...eet.functions/
msg/e249f6c074a3adfd
> (one line in your browser)
>
> But google is adding extra characters in the code and screwing it up.
>
> So I thought that a link to Harlan's FTP site would be better. But I just
> looked at it and it's not up to date with what he's posted on the
newsgroups.
>
> So I used the (most???) current version that I saw on google and tried to
clean
> up those google induced errors.
>
> I tested it to make sure it works with a call like:
>
> =pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
>
> Here's Harlan's code (but you'll need to still make that instrrev97
change):
>
> Option Explicit
> 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
>
>
> CLR wrote:
> >
> > Thanks Dave.........yeah, I finally did that but still no
joy...........it
> > don't give error messages any more, but it also don't give
> > results........depending on what I type in the =PULL(), I usually get
> > #VALUE! or #REF!..........I've tried on both 97 and 2000.........I've
even
> > got both May 05 updates and still cant seem to get it to work.......I
guess
> > maybe it's time to start back at square one..............some days the
> > Dragon wins........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > > You'll have to do the same kind of thing.
> > >
> > > n = InStrRev(Len(xref), xref, "!")
> > > becomes
> > > n = InStrRev97(expr, "!")
> > >
> > >
> > >
> > >
> > > CLR wrote:
> > > >
> > > > Thanks Dave.........it got past that line by following your
> > instructions, but
> > > > now stops on
> > > >
> > > > n = InStrRev(Len(xref), xref, "!")
> > > >
> > > > I tried modifying it, but no joy.....I'm just shooting in the dark.
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > Instrrev was added in xl2k.
> > > > >
> > > > > You could create your own InstrRev97 function and use that:
> > > > >
> > > > > Function InStrRev97(mystr As Variant, mydelim As String) As Long
> > > > > Dim i As Long
> > > > > InStrRev97 = 0
> > > > > For i = Len(mystr) To 1 Step -1
> > > > > If Mid(mystr, i, 1) = mydelim Then
> > > > > InStrRev97 = i
> > > > > Exit Function
> > > > > End If
> > > > > Next i
> > > > > End Function
> > > > >
> > > > > (Just add this to the bottom of that general module.
> > > > >
> > > > > so
> > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > becomes
> > > > > n = InStrRev97(expr, "\")
> > > > >
> > > > >
> > > > >
> > > > > =====
> > > > > There are some other functions that were added in xl2k (split for
> > example). But
> > > > > I took a cursory glance at Harlan's code and didn't see any others
> > that would
> > > > > cause you trouble.
> > > > >
> > > > > (Post back when you see that I missed one!)
> > > > >
> > > > > CLR wrote:
> > > > > >
> > > > > > I went after Harlan's UDF and got it but can't for the life of
me
> > figure out
> > > > > > how to use it.
> > > > > > I pasted it into a regular module and, all I can get "Sub or
> > function not
> > > > > > defined" on this line..
> > > > > >
> > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > >
> > > > > > Anybody see what I'm doing wrong?
> > > > > >
> > > > > > Vaya con Dios,
> > > > > > Chuck, CABGx3
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > You'd want to use the =indirect() worksheet function. But
that
> > doesn't work
> > > > > > > with closed files.
> > > > > > >
> > > > > > > Harlan Grove wrote a UDF called PULL that will retrieve the
value
> > from a closed
> > > > > > > workbook.
> > > > > > >
> > > > > > > You can find the function at Harlan's FTP site:
> > > > > > > ftp://members.aol.com/hrlngrv/
> > > > > > >
> > > > > > > CLR wrote:
> > > > > > > >
> > > > > > > > Hi All........
> > > > > > > >
> > > > > > > > I am trying to concatenate two cells together to form a
filename
> > in a
> > > > > > > > link............no joy, .......all I get is "That filename
is
> > not
> > > > > > > > valid"........
> > > > > > > >
> > > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > > >
> > > > > > > > C6 is a name, as Jones, Fred
> > > > > > > > B6 is a string as R1938
> > > > > > > >
> > > > > > > > The filename I 'm looking for is Jones,Fred_R1938.xls and it
> > does
> > > > > > > > exist........and of course works if I hard code the filename
> > into the
> > > > > > > > formula........
> > > > > > > >
> > > > > > > > =clean(c6)&"_"&b6 works fine in a test of that standalone
part,
> > but not in
> > > > > > > > the link formula
> > > > > > > >
> > > > > > > > Any help would be appreciated........
> > > > > > > >
> > > > > > > > Vaya con Dios,
> > > > > > > > Chuck, CABGx3
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

One more word of warning. If you're returning any strings longer than 255
characters, this won't retrieve all the text (it's limited to 255 characters).

CLR wrote:
>
> Thanks Dave.............I'll have a go at it tomorrow........I'm totally
> burnt out tonight..........fresh start in the morning and it will probably
> fall right in to place......just can't see the forrest for the trees
> tonight..........
>
> I do appreciate your time........this is actually quite important to me.
> I'm starting a new project and this feature plays a major part.....I just
> need to calm down and make it work. I'll have maybe 300 of these PULL's on
> each of about 50 Training Matrix Workbooks........they will actually be
> inside concatenated VLOOKUPs, and will draw from 500-600 individual
> employee files......and the whole thing tied together with a few menu's and
> a little VBA........fun for me, and I learn something new
> everyday............
>
> Many, many thanks again,
> Vaya con Dios,
> Chuck, CABGx3
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42EAC719.67F023E3@verizonXSPAM.net...
> > I was going to give you a google post:
> >
> http://groups.google.co.uk/group/mic...eet.functions/
> msg/e249f6c074a3adfd
> > (one line in your browser)
> >
> > But google is adding extra characters in the code and screwing it up.
> >
> > So I thought that a link to Harlan's FTP site would be better. But I just
> > looked at it and it's not up to date with what he's posted on the
> newsgroups.
> >
> > So I used the (most???) current version that I saw on google and tried to
> clean
> > up those google induced errors.
> >
> > I tested it to make sure it works with a call like:
> >
> > =pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
> >
> > Here's Harlan's code (but you'll need to still make that instrrev97
> change):
> >
> > Option Explicit
> > 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
> >
> >
> > CLR wrote:
> > >
> > > Thanks Dave.........yeah, I finally did that but still no
> joy...........it
> > > don't give error messages any more, but it also don't give
> > > results........depending on what I type in the =PULL(), I usually get
> > > #VALUE! or #REF!..........I've tried on both 97 and 2000.........I've
> even
> > > got both May 05 updates and still cant seem to get it to work.......I
> guess
> > > maybe it's time to start back at square one..............some days the
> > > Dragon wins........
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > > > You'll have to do the same kind of thing.
> > > >
> > > > n = InStrRev(Len(xref), xref, "!")
> > > > becomes
> > > > n = InStrRev97(expr, "!")
> > > >
> > > >
> > > >
> > > >
> > > > CLR wrote:
> > > > >
> > > > > Thanks Dave.........it got past that line by following your
> > > instructions, but
> > > > > now stops on
> > > > >
> > > > > n = InStrRev(Len(xref), xref, "!")
> > > > >
> > > > > I tried modifying it, but no joy.....I'm just shooting in the dark.
> > > > >
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > Instrrev was added in xl2k.
> > > > > >
> > > > > > You could create your own InstrRev97 function and use that:
> > > > > >
> > > > > > Function InStrRev97(mystr As Variant, mydelim As String) As Long
> > > > > > Dim i As Long
> > > > > > InStrRev97 = 0
> > > > > > For i = Len(mystr) To 1 Step -1
> > > > > > If Mid(mystr, i, 1) = mydelim Then
> > > > > > InStrRev97 = i
> > > > > > Exit Function
> > > > > > End If
> > > > > > Next i
> > > > > > End Function
> > > > > >
> > > > > > (Just add this to the bottom of that general module.
> > > > > >
> > > > > > so
> > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > becomes
> > > > > > n = InStrRev97(expr, "\")
> > > > > >
> > > > > >
> > > > > >
> > > > > > =====
> > > > > > There are some other functions that were added in xl2k (split for
> > > example). But
> > > > > > I took a cursory glance at Harlan's code and didn't see any others
> > > that would
> > > > > > cause you trouble.
> > > > > >
> > > > > > (Post back when you see that I missed one!)
> > > > > >
> > > > > > CLR wrote:
> > > > > > >
> > > > > > > I went after Harlan's UDF and got it but can't for the life of
> me
> > > figure out
> > > > > > > how to use it.
> > > > > > > I pasted it into a regular module and, all I can get "Sub or
> > > function not
> > > > > > > defined" on this line..
> > > > > > >
> > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > >
> > > > > > > Anybody see what I'm doing wrong?
> > > > > > >
> > > > > > > Vaya con Dios,
> > > > > > > Chuck, CABGx3
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > You'd want to use the =indirect() worksheet function. But
> that
> > > doesn't work
> > > > > > > > with closed files.
> > > > > > > >
> > > > > > > > Harlan Grove wrote a UDF called PULL that will retrieve the
> value
> > > from a closed
> > > > > > > > workbook.
> > > > > > > >
> > > > > > > > You can find the function at Harlan's FTP site:
> > > > > > > > ftp://members.aol.com/hrlngrv/
> > > > > > > >
> > > > > > > > CLR wrote:
> > > > > > > > >
> > > > > > > > > Hi All........
> > > > > > > > >
> > > > > > > > > I am trying to concatenate two cells together to form a
> filename
> > > in a
> > > > > > > > > link............no joy, .......all I get is "That filename
> is
> > > not
> > > > > > > > > valid"........
> > > > > > > > >
> > > > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > > > >
> > > > > > > > > C6 is a name, as Jones, Fred
> > > > > > > > > B6 is a string as R1938
> > > > > > > > >
> > > > > > > > > The filename I 'm looking for is Jones,Fred_R1938.xls and it
> > > does
> > > > > > > > > exist........and of course works if I hard code the filename
> > > into the
> > > > > > > > > formula........
> > > > > > > > >
> > > > > > > > > =clean(c6)&"_"&b6 works fine in a test of that standalone
> part,
> > > but not in
> > > > > > > > > the link formula
> > > > > > > > >
> > > > > > > > > Any help would be appreciated........
> > > > > > > > >
> > > > > > > > > Vaya con Dios,
> > > > > > > > > Chuck, CABGx3
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

That will not be a problem........will only be retrieving from 1-10
characters or so, but thanks for the heads-up..........how the heck do you
guys find out about stuff like that anyway?

As for the problem...........I started from scratch with your version of
Harlan's code and your 97 thing and all I could get was #NAME?............I
went back and forth between 97 and 2k and switching the InStrRev thing back
and forth and all results were the same........#NAME?

I even started with a new Book, and re-created the same path you used in
your test and copied and pasted your test formula over to my book and tried
97 and 2k with and without the InStrRev thing and with the Book2 file open
and closed and it still did the same thing.........#NAME?, and you know
what?, it comes in as a merged cell 16 columns wide, whereas the Book2 cell
A1 is only one column wide.

Obviously it's something I'm doing wrong, but I can't for the life of me see
what it might be..............maybe if you were to tell me the EXACT steps
you use to test it and I could follow them and see if I get the same
results........

Thanks for all your help,
Vaya con Dios,
Chuck, CABGx3




"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42EAD25B.64DEA484@verizonXSPAM.net...
> One more word of warning. If you're returning any strings longer than 255
> characters, this won't retrieve all the text (it's limited to 255
characters).
>
> CLR wrote:
> >
> > Thanks Dave.............I'll have a go at it tomorrow........I'm totally
> > burnt out tonight..........fresh start in the morning and it will
probably
> > fall right in to place......just can't see the forrest for the trees
> > tonight..........
> >
> > I do appreciate your time........this is actually quite important to me.
> > I'm starting a new project and this feature plays a major part.....I
just
> > need to calm down and make it work. I'll have maybe 300 of these PULL's
on
> > each of about 50 Training Matrix Workbooks........they will actually be
> > inside concatenated VLOOKUPs, and will draw from 500-600 individual
> > employee files......and the whole thing tied together with a few menu's
and
> > a little VBA........fun for me, and I learn something new
> > everyday............
> >
> > Many, many thanks again,
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:42EAC719.67F023E3@verizonXSPAM.net...
> > > I was going to give you a google post:
> > >
> >
http://groups.google.co.uk/group/mic...eet.functions/
> > msg/e249f6c074a3adfd
> > > (one line in your browser)
> > >
> > > But google is adding extra characters in the code and screwing it up.
> > >
> > > So I thought that a link to Harlan's FTP site would be better. But I
just
> > > looked at it and it's not up to date with what he's posted on the
> > newsgroups.
> > >
> > > So I used the (most???) current version that I saw on google and tried
to
> > clean
> > > up those google induced errors.
> > >
> > > I tested it to make sure it works with a call like:
> > >
> > > =pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
> > >
> > > Here's Harlan's code (but you'll need to still make that instrrev97
> > change):
> > >
> > > Option Explicit
> > > 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
> > >
> > >
> > > CLR wrote:
> > > >
> > > > Thanks Dave.........yeah, I finally did that but still no
> > joy...........it
> > > > don't give error messages any more, but it also don't give
> > > > results........depending on what I type in the =PULL(), I usually
get
> > > > #VALUE! or #REF!..........I've tried on both 97 and
2000.........I've
> > even
> > > > got both May 05 updates and still cant seem to get it to
work.......I
> > guess
> > > > maybe it's time to start back at square one..............some days
the
> > > > Dragon wins........
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > > > > You'll have to do the same kind of thing.
> > > > >
> > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > becomes
> > > > > n = InStrRev97(expr, "!")
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > CLR wrote:
> > > > > >
> > > > > > Thanks Dave.........it got past that line by following your
> > > > instructions, but
> > > > > > now stops on
> > > > > >
> > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > >
> > > > > > I tried modifying it, but no joy.....I'm just shooting in the
dark.
> > > > > >
> > > > > > Vaya con Dios,
> > > > > > Chuck, CABGx3
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > Instrrev was added in xl2k.
> > > > > > >
> > > > > > > You could create your own InstrRev97 function and use that:
> > > > > > >
> > > > > > > Function InStrRev97(mystr As Variant, mydelim As String) As
Long
> > > > > > > Dim i As Long
> > > > > > > InStrRev97 = 0
> > > > > > > For i = Len(mystr) To 1 Step -1
> > > > > > > If Mid(mystr, i, 1) = mydelim Then
> > > > > > > InStrRev97 = i
> > > > > > > Exit Function
> > > > > > > End If
> > > > > > > Next i
> > > > > > > End Function
> > > > > > >
> > > > > > > (Just add this to the bottom of that general module.
> > > > > > >
> > > > > > > so
> > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > becomes
> > > > > > > n = InStrRev97(expr, "\")
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > =====
> > > > > > > There are some other functions that were added in xl2k (split
for
> > > > example). But
> > > > > > > I took a cursory glance at Harlan's code and didn't see any
others
> > > > that would
> > > > > > > cause you trouble.
> > > > > > >
> > > > > > > (Post back when you see that I missed one!)
> > > > > > >
> > > > > > > CLR wrote:
> > > > > > > >
> > > > > > > > I went after Harlan's UDF and got it but can't for the life
of
> > me
> > > > figure out
> > > > > > > > how to use it.
> > > > > > > > I pasted it into a regular module and, all I can get "Sub or
> > > > function not
> > > > > > > > defined" on this line..
> > > > > > > >
> > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > >
> > > > > > > > Anybody see what I'm doing wrong?
> > > > > > > >
> > > > > > > > Vaya con Dios,
> > > > > > > > Chuck, CABGx3
> > > > > > > >
> > > > > > > > "Dave Peterson" wrote:
> > > > > > > >
> > > > > > > > > You'd want to use the =indirect() worksheet function. But
> > that
> > > > doesn't work
> > > > > > > > > with closed files.
> > > > > > > > >
> > > > > > > > > Harlan Grove wrote a UDF called PULL that will retrieve
the
> > value
> > > > from a closed
> > > > > > > > > workbook.
> > > > > > > > >
> > > > > > > > > You can find the function at Harlan's FTP site:
> > > > > > > > > ftp://members.aol.com/hrlngrv/
> > > > > > > > >
> > > > > > > > > CLR wrote:
> > > > > > > > > >
> > > > > > > > > > Hi All........
> > > > > > > > > >
> > > > > > > > > > I am trying to concatenate two cells together to form a
> > filename
> > > > in a
> > > > > > > > > > link............no joy, .......all I get is "That
filename
> > is
> > > > not
> > > > > > > > > > valid"........
> > > > > > > > > >
> > > > > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > > > > >
> > > > > > > > > > C6 is a name, as Jones, Fred
> > > > > > > > > > B6 is a string as R1938
> > > > > > > > > >
> > > > > > > > > > The filename I 'm looking for is Jones,Fred_R1938.xls
and it
> > > > does
> > > > > > > > > > exist........and of course works if I hard code the
filename
> > > > into the
> > > > > > > > > > formula........
> > > > > > > > > >
> > > > > > > > > > =clean(c6)&"_"&b6 works fine in a test of that
standalone
> > part,
> > > > but not in
> > > > > > > > > > the link formula
> > > > > > > > > >
> > > > > > > > > > Any help would be appreciated........
> > > > > > > > > >
> > > > > > > > > > Vaya con Dios,
> > > > > > > > > > Chuck, CABGx3
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > Dave Peterson
> > > > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

The usual things that can cause the #name? errors:

1. You spelled the function one way in the worksheet cell and a different way
in your code.
2. You put the code in the wrong spot--it belongs in a General module (not
behind a worksheet, not behind ThisWorkbook).
3. Maybe you put the function in a different workbook's project by mistake?

I'd fix that #name? error before looking for more. But functions don't bring
back formats. I'd suspect that the cell was formatted (as merged) before you
started.



CLR wrote:
>
> That will not be a problem........will only be retrieving from 1-10
> characters or so, but thanks for the heads-up..........how the heck do you
> guys find out about stuff like that anyway?
>
> As for the problem...........I started from scratch with your version of
> Harlan's code and your 97 thing and all I could get was #NAME?............I
> went back and forth between 97 and 2k and switching the InStrRev thing back
> and forth and all results were the same........#NAME?
>
> I even started with a new Book, and re-created the same path you used in
> your test and copied and pasted your test formula over to my book and tried
> 97 and 2k with and without the InStrRev thing and with the Book2 file open
> and closed and it still did the same thing.........#NAME?, and you know
> what?, it comes in as a merged cell 16 columns wide, whereas the Book2 cell
> A1 is only one column wide.
>
> Obviously it's something I'm doing wrong, but I can't for the life of me see
> what it might be..............maybe if you were to tell me the EXACT steps
> you use to test it and I could follow them and see if I get the same
> results........
>
> Thanks for all your help,
> Vaya con Dios,
> Chuck, CABGx3
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42EAD25B.64DEA484@verizonXSPAM.net...
> > One more word of warning. If you're returning any strings longer than 255
> > characters, this won't retrieve all the text (it's limited to 255
> characters).
> >
> > CLR wrote:
> > >
> > > Thanks Dave.............I'll have a go at it tomorrow........I'm totally
> > > burnt out tonight..........fresh start in the morning and it will
> probably
> > > fall right in to place......just can't see the forrest for the trees
> > > tonight..........
> > >
> > > I do appreciate your time........this is actually quite important to me.
> > > I'm starting a new project and this feature plays a major part.....I
> just
> > > need to calm down and make it work. I'll have maybe 300 of these PULL's
> on
> > > each of about 50 Training Matrix Workbooks........they will actually be
> > > inside concatenated VLOOKUPs, and will draw from 500-600 individual
> > > employee files......and the whole thing tied together with a few menu's
> and
> > > a little VBA........fun for me, and I learn something new
> > > everyday............
> > >
> > > Many, many thanks again,
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:42EAC719.67F023E3@verizonXSPAM.net...
> > > > I was going to give you a google post:
> > > >
> > >
> http://groups.google.co.uk/group/mic...eet.functions/
> > > msg/e249f6c074a3adfd
> > > > (one line in your browser)
> > > >
> > > > But google is adding extra characters in the code and screwing it up.
> > > >
> > > > So I thought that a link to Harlan's FTP site would be better. But I
> just
> > > > looked at it and it's not up to date with what he's posted on the
> > > newsgroups.
> > > >
> > > > So I used the (most???) current version that I saw on google and tried
> to
> > > clean
> > > > up those google induced errors.
> > > >
> > > > I tested it to make sure it works with a call like:
> > > >
> > > > =pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
> > > >
> > > > Here's Harlan's code (but you'll need to still make that instrrev97
> > > change):
> > > >
> > > > Option Explicit
> > > > 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
> > > >
> > > >
> > > > CLR wrote:
> > > > >
> > > > > Thanks Dave.........yeah, I finally did that but still no
> > > joy...........it
> > > > > don't give error messages any more, but it also don't give
> > > > > results........depending on what I type in the =PULL(), I usually
> get
> > > > > #VALUE! or #REF!..........I've tried on both 97 and
> 2000.........I've
> > > even
> > > > > got both May 05 updates and still cant seem to get it to
> work.......I
> > > guess
> > > > > maybe it's time to start back at square one..............some days
> the
> > > > > Dragon wins........
> > > > >
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > > > > > You'll have to do the same kind of thing.
> > > > > >
> > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > becomes
> > > > > > n = InStrRev97(expr, "!")
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > CLR wrote:
> > > > > > >
> > > > > > > Thanks Dave.........it got past that line by following your
> > > > > instructions, but
> > > > > > > now stops on
> > > > > > >
> > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > >
> > > > > > > I tried modifying it, but no joy.....I'm just shooting in the
> dark.
> > > > > > >
> > > > > > > Vaya con Dios,
> > > > > > > Chuck, CABGx3
> > > > > > >
> > > > > > > "Dave Peterson" wrote:
> > > > > > >
> > > > > > > > Instrrev was added in xl2k.
> > > > > > > >
> > > > > > > > You could create your own InstrRev97 function and use that:
> > > > > > > >
> > > > > > > > Function InStrRev97(mystr As Variant, mydelim As String) As
> Long
> > > > > > > > Dim i As Long
> > > > > > > > InStrRev97 = 0
> > > > > > > > For i = Len(mystr) To 1 Step -1
> > > > > > > > If Mid(mystr, i, 1) = mydelim Then
> > > > > > > > InStrRev97 = i
> > > > > > > > Exit Function
> > > > > > > > End If
> > > > > > > > Next i
> > > > > > > > End Function
> > > > > > > >
> > > > > > > > (Just add this to the bottom of that general module.
> > > > > > > >
> > > > > > > > so
> > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > becomes
> > > > > > > > n = InStrRev97(expr, "\")
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > =====
> > > > > > > > There are some other functions that were added in xl2k (split
> for
> > > > > example). But
> > > > > > > > I took a cursory glance at Harlan's code and didn't see any
> others
> > > > > that would
> > > > > > > > cause you trouble.
> > > > > > > >
> > > > > > > > (Post back when you see that I missed one!)
> > > > > > > >
> > > > > > > > CLR wrote:
> > > > > > > > >
> > > > > > > > > I went after Harlan's UDF and got it but can't for the life
> of
> > > me
> > > > > figure out
> > > > > > > > > how to use it.
> > > > > > > > > I pasted it into a regular module and, all I can get "Sub or
> > > > > function not
> > > > > > > > > defined" on this line..
> > > > > > > > >
> > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > >
> > > > > > > > > Anybody see what I'm doing wrong?
> > > > > > > > >
> > > > > > > > > Vaya con Dios,
> > > > > > > > > Chuck, CABGx3
> > > > > > > > >
> > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > >
> > > > > > > > > > You'd want to use the =indirect() worksheet function. But
> > > that
> > > > > doesn't work
> > > > > > > > > > with closed files.
> > > > > > > > > >
> > > > > > > > > > Harlan Grove wrote a UDF called PULL that will retrieve
> the
> > > value
> > > > > from a closed
> > > > > > > > > > workbook.
> > > > > > > > > >
> > > > > > > > > > You can find the function at Harlan's FTP site:
> > > > > > > > > > ftp://members.aol.com/hrlngrv/
> > > > > > > > > >
> > > > > > > > > > CLR wrote:
> > > > > > > > > > >
> > > > > > > > > > > Hi All........
> > > > > > > > > > >
> > > > > > > > > > > I am trying to concatenate two cells together to form a
> > > filename
> > > > > in a
> > > > > > > > > > > link............no joy, .......all I get is "That
> filename
> > > is
> > > > > not
> > > > > > > > > > > valid"........
> > > > > > > > > > >
> > > > > > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > > > > > >
> > > > > > > > > > > C6 is a name, as Jones, Fred
> > > > > > > > > > > B6 is a string as R1938
> > > > > > > > > > >
> > > > > > > > > > > The filename I 'm looking for is Jones,Fred_R1938.xls
> and it
> > > > > does
> > > > > > > > > > > exist........and of course works if I hard code the
> filename
> > > > > into the
> > > > > > > > > > > formula........
> > > > > > > > > > >
> > > > > > > > > > > =clean(c6)&"_"&b6 works fine in a test of that
> standalone
> > > part,
> > > > > but not in
> > > > > > > > > > > the link formula
> > > > > > > > > > >
> > > > > > > > > > > Any help would be appreciated........
> > > > > > > > > > >
> > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > Dave Peterson
> > > > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > > > >
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Ok..........for some reason the FORMAT problem seemed to come when I copied
and pasted the invocation line from the Post to my workbook........don't
understand that, it never happened before, but I'm over it......that's no
longer a problem.

I just now opened a new workbook in XL97 . I then typed "Success, PULL
test" without quotes, in A1 and saved it directly to my C:\ as PullTest.xls

I then opened a new workbook (PullMaster.xls) and copied and pasted Harlan's
code from your Post into Module1, and then copied and pasted your 97 code
from your Post to the bottom of that same module, and changed the two lines
in Harlan's code from InStrRev to InStrRev97 and I then hand typed this in
A5
=pull("'C:\[PullTest.xls]Sheet1'!$A$1") My result was #REF!

I then closed PullMaster.xls and re-opened it in XL2k. Cell A5 opens as
#VALUE! so I then opened Module 1 and set the InStrRev lines in Harlan's
code back to their original way, and cell A5 still reads
#VALUE!.............

I was just about to post this message and decided to open PullTest.xls in
the background. Well, I did and A5 stayed as #VALUE! but when I deleted
the equal sign, and then re-inserted it again, the PULL function worked and
I got my value from PullTest.xls cell A1 that I was supposed to
get......only problem being is that the file I go after has to be
OPEN..........

I switched back to XL97 , re-set the InStrRev lines and the same
thing.......I started off getting the same #REF!, instead of the #VALUE! I
got in 2k, and when I opened PullTest.xls in the background, and re=cycled
the Pull formulas, it all worked correctly, but only with the external file
OPEN.......which is what INDIRECT does.........

Can you see if I have done anything wrong to make it not work with CLOSED
files?.........that is what I thought it was supposed to do, and what I need
it to do if possible.......


Vaya con Dios,
Chuck, CABGx3












"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42EBDA67.AC772A4@verizonXSPAM.net...
> The usual things that can cause the #name? errors:
>
> 1. You spelled the function one way in the worksheet cell and a different
way
> in your code.
> 2. You put the code in the wrong spot--it belongs in a General module
(not
> behind a worksheet, not behind ThisWorkbook).
> 3. Maybe you put the function in a different workbook's project by
mistake?
>
> I'd fix that #name? error before looking for more. But functions don't
bring
> back formats. I'd suspect that the cell was formatted (as merged) before
you
> started.
>
>
>
> CLR wrote:
> >
> > That will not be a problem........will only be retrieving from 1-10
> > characters or so, but thanks for the heads-up..........how the heck do
you
> > guys find out about stuff like that anyway?
> >
> > As for the problem...........I started from scratch with your version of
> > Harlan's code and your 97 thing and all I could get was
#NAME?............I
> > went back and forth between 97 and 2k and switching the InStrRev thing
back
> > and forth and all results were the same........#NAME?
> >
> > I even started with a new Book, and re-created the same path you used in
> > your test and copied and pasted your test formula over to my book and
tried
> > 97 and 2k with and without the InStrRev thing and with the Book2 file
open
> > and closed and it still did the same thing.........#NAME?, and you know
> > what?, it comes in as a merged cell 16 columns wide, whereas the Book2
cell
> > A1 is only one column wide.
> >
> > Obviously it's something I'm doing wrong, but I can't for the life of me
see
> > what it might be..............maybe if you were to tell me the EXACT
steps
> > you use to test it and I could follow them and see if I get the same
> > results........
> >
> > Thanks for all your help,
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:42EAD25B.64DEA484@verizonXSPAM.net...
> > > One more word of warning. If you're returning any strings longer than
255
> > > characters, this won't retrieve all the text (it's limited to 255
> > characters).
> > >
> > > CLR wrote:
> > > >
> > > > Thanks Dave.............I'll have a go at it tomorrow........I'm
totally
> > > > burnt out tonight..........fresh start in the morning and it will
> > probably
> > > > fall right in to place......just can't see the forrest for the trees
> > > > tonight..........
> > > >
> > > > I do appreciate your time........this is actually quite important to
me.
> > > > I'm starting a new project and this feature plays a major part.....I
> > just
> > > > need to calm down and make it work. I'll have maybe 300 of these
PULL's
> > on
> > > > each of about 50 Training Matrix Workbooks........they will actually
be
> > > > inside concatenated VLOOKUPs, and will draw from 500-600 individual
> > > > employee files......and the whole thing tied together with a few
menu's
> > and
> > > > a little VBA........fun for me, and I learn something new
> > > > everyday............
> > > >
> > > > Many, many thanks again,
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > news:42EAC719.67F023E3@verizonXSPAM.net...
> > > > > I was going to give you a google post:
> > > > >
> > > >
> >
http://groups.google.co.uk/group/mic...eet.functions/
> > > > msg/e249f6c074a3adfd
> > > > > (one line in your browser)
> > > > >
> > > > > But google is adding extra characters in the code and screwing it
up.
> > > > >
> > > > > So I thought that a link to Harlan's FTP site would be better.
But I
> > just
> > > > > looked at it and it's not up to date with what he's posted on the
> > > > newsgroups.
> > > > >
> > > > > So I used the (most???) current version that I saw on google and
tried
> > to
> > > > clean
> > > > > up those google induced errors.
> > > > >
> > > > > I tested it to make sure it works with a call like:
> > > > >
> > > > > =pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
> > > > >
> > > > > Here's Harlan's code (but you'll need to still make that
instrrev97
> > > > change):
> > > > >
> > > > > Option Explicit
> > > > > 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
> > > > >
> > > > >
> > > > > CLR wrote:
> > > > > >
> > > > > > Thanks Dave.........yeah, I finally did that but still no
> > > > joy...........it
> > > > > > don't give error messages any more, but it also don't give
> > > > > > results........depending on what I type in the =PULL(), I
usually
> > get
> > > > > > #VALUE! or #REF!..........I've tried on both 97 and
> > 2000.........I've
> > > > even
> > > > > > got both May 05 updates and still cant seem to get it to
> > work.......I
> > > > guess
> > > > > > maybe it's time to start back at square one..............some
days
> > the
> > > > > > Dragon wins........
> > > > > >
> > > > > > Vaya con Dios,
> > > > > > Chuck, CABGx3
> > > > > >
> > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > > news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > > > > > > You'll have to do the same kind of thing.
> > > > > > >
> > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > becomes
> > > > > > > n = InStrRev97(expr, "!")
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > CLR wrote:
> > > > > > > >
> > > > > > > > Thanks Dave.........it got past that line by following your
> > > > > > instructions, but
> > > > > > > > now stops on
> > > > > > > >
> > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > >
> > > > > > > > I tried modifying it, but no joy.....I'm just shooting in
the
> > dark.
> > > > > > > >
> > > > > > > > Vaya con Dios,
> > > > > > > > Chuck, CABGx3
> > > > > > > >
> > > > > > > > "Dave Peterson" wrote:
> > > > > > > >
> > > > > > > > > Instrrev was added in xl2k.
> > > > > > > > >
> > > > > > > > > You could create your own InstrRev97 function and use
that:
> > > > > > > > >
> > > > > > > > > Function InStrRev97(mystr As Variant, mydelim As String)
As
> > Long
> > > > > > > > > Dim i As Long
> > > > > > > > > InStrRev97 = 0
> > > > > > > > > For i = Len(mystr) To 1 Step -1
> > > > > > > > > If Mid(mystr, i, 1) = mydelim Then
> > > > > > > > > InStrRev97 = i
> > > > > > > > > Exit Function
> > > > > > > > > End If
> > > > > > > > > Next i
> > > > > > > > > End Function
> > > > > > > > >
> > > > > > > > > (Just add this to the bottom of that general module.
> > > > > > > > >
> > > > > > > > > so
> > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > becomes
> > > > > > > > > n = InStrRev97(expr, "\")
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > =====
> > > > > > > > > There are some other functions that were added in xl2k
(split
> > for
> > > > > > example). But
> > > > > > > > > I took a cursory glance at Harlan's code and didn't see
any
> > others
> > > > > > that would
> > > > > > > > > cause you trouble.
> > > > > > > > >
> > > > > > > > > (Post back when you see that I missed one!)
> > > > > > > > >
> > > > > > > > > CLR wrote:
> > > > > > > > > >
> > > > > > > > > > I went after Harlan's UDF and got it but can't for the
life
> > of
> > > > me
> > > > > > figure out
> > > > > > > > > > how to use it.
> > > > > > > > > > I pasted it into a regular module and, all I can get
"Sub or
> > > > > > function not
> > > > > > > > > > defined" on this line..
> > > > > > > > > >
> > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > >
> > > > > > > > > > Anybody see what I'm doing wrong?
> > > > > > > > > >
> > > > > > > > > > Vaya con Dios,
> > > > > > > > > > Chuck, CABGx3
> > > > > > > > > >
> > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > >
> > > > > > > > > > > You'd want to use the =indirect() worksheet function.
But
> > > > that
> > > > > > doesn't work
> > > > > > > > > > > with closed files.
> > > > > > > > > > >
> > > > > > > > > > > Harlan Grove wrote a UDF called PULL that will
retrieve
> > the
> > > > value
> > > > > > from a closed
> > > > > > > > > > > workbook.
> > > > > > > > > > >
> > > > > > > > > > > You can find the function at Harlan's FTP site:
> > > > > > > > > > > ftp://members.aol.com/hrlngrv/
> > > > > > > > > > >
> > > > > > > > > > > CLR wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > Hi All........
> > > > > > > > > > > >
> > > > > > > > > > > > I am trying to concatenate two cells together to
form a
> > > > filename
> > > > > > in a
> > > > > > > > > > > > link............no joy, .......all I get is "That
> > filename
> > > > is
> > > > > > not
> > > > > > > > > > > > valid"........
> > > > > > > > > > > >
> > > > > > > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > > > > > > >
> > > > > > > > > > > > C6 is a name, as Jones, Fred
> > > > > > > > > > > > B6 is a string as R1938
> > > > > > > > > > > >
> > > > > > > > > > > > The filename I 'm looking for is
Jones,Fred_R1938.xls
> > and it
> > > > > > does
> > > > > > > > > > > > exist........and of course works if I hard code the
> > filename
> > > > > > into the
> > > > > > > > > > > > formula........
> > > > > > > > > > > >
> > > > > > > > > > > > =clean(c6)&"_"&b6 works fine in a test of that
> > standalone
> > > > part,
> > > > > > but not in
> > > > > > > > > > > > the link formula
> > > > > > > > > > > >
> > > > > > > > > > > > Any help would be appreciated........
> > > > > > > > > > > >
> > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > >
> > > > > > > > > > > Dave Peterson
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > Dave Peterson
> > > > > > > > >
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

I did the same thing you did -- I created a c:\pulltest.xls in xl2003 and
everything worked ok.


I think there is still one typo in Harlan's code (but it didn't affect my test
of your technique).

This line:

n = InStrRev(Len(xref), xref, "!")
should be:
n = InStrRev(xref, "!")

I guess the next thing to try is to reopen pullmaster.xls in xl97. Change those
instrrev to instrrev97.

Then put a break point on the first executable line in the function.

This line:
n = InStrRev97(xref, "\")

Then open pulltest.xls. Select one of the offending cells and hit F2, then
enter.

The function should start, then stop on that break point line. Then you can F8
through the code to find what breaks.

CLR wrote:
>
> Ok..........for some reason the FORMAT problem seemed to come when I copied
> and pasted the invocation line from the Post to my workbook........don't
> understand that, it never happened before, but I'm over it......that's no
> longer a problem.
>
> I just now opened a new workbook in XL97 . I then typed "Success, PULL
> test" without quotes, in A1 and saved it directly to my C:\ as PullTest.xls
>
> I then opened a new workbook (PullMaster.xls) and copied and pasted Harlan's
> code from your Post into Module1, and then copied and pasted your 97 code
> from your Post to the bottom of that same module, and changed the two lines
> in Harlan's code from InStrRev to InStrRev97 and I then hand typed this in
> A5
> =pull("'C:\[PullTest.xls]Sheet1'!$A$1") My result was #REF!
>
> I then closed PullMaster.xls and re-opened it in XL2k. Cell A5 opens as
> #VALUE! so I then opened Module 1 and set the InStrRev lines in Harlan's
> code back to their original way, and cell A5 still reads
> #VALUE!.............
>
> I was just about to post this message and decided to open PullTest.xls in
> the background. Well, I did and A5 stayed as #VALUE! but when I deleted
> the equal sign, and then re-inserted it again, the PULL function worked and
> I got my value from PullTest.xls cell A1 that I was supposed to
> get......only problem being is that the file I go after has to be
> OPEN..........
>
> I switched back to XL97 , re-set the InStrRev lines and the same
> thing.......I started off getting the same #REF!, instead of the #VALUE! I
> got in 2k, and when I opened PullTest.xls in the background, and re=cycled
> the Pull formulas, it all worked correctly, but only with the external file
> OPEN.......which is what INDIRECT does.........
>
> Can you see if I have done anything wrong to make it not work with CLOSED
> files?.........that is what I thought it was supposed to do, and what I need
> it to do if possible.......
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42EBDA67.AC772A4@verizonXSPAM.net...
> > The usual things that can cause the #name? errors:
> >
> > 1. You spelled the function one way in the worksheet cell and a different
> way
> > in your code.
> > 2. You put the code in the wrong spot--it belongs in a General module
> (not
> > behind a worksheet, not behind ThisWorkbook).
> > 3. Maybe you put the function in a different workbook's project by
> mistake?
> >
> > I'd fix that #name? error before looking for more. But functions don't
> bring
> > back formats. I'd suspect that the cell was formatted (as merged) before
> you
> > started.
> >
> >
> >
> > CLR wrote:
> > >
> > > That will not be a problem........will only be retrieving from 1-10
> > > characters or so, but thanks for the heads-up..........how the heck do
> you
> > > guys find out about stuff like that anyway?
> > >
> > > As for the problem...........I started from scratch with your version of
> > > Harlan's code and your 97 thing and all I could get was
> #NAME?............I
> > > went back and forth between 97 and 2k and switching the InStrRev thing
> back
> > > and forth and all results were the same........#NAME?
> > >
> > > I even started with a new Book, and re-created the same path you used in
> > > your test and copied and pasted your test formula over to my book and
> tried
> > > 97 and 2k with and without the InStrRev thing and with the Book2 file
> open
> > > and closed and it still did the same thing.........#NAME?, and you know
> > > what?, it comes in as a merged cell 16 columns wide, whereas the Book2
> cell
> > > A1 is only one column wide.
> > >
> > > Obviously it's something I'm doing wrong, but I can't for the life of me
> see
> > > what it might be..............maybe if you were to tell me the EXACT
> steps
> > > you use to test it and I could follow them and see if I get the same
> > > results........
> > >
> > > Thanks for all your help,
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:42EAD25B.64DEA484@verizonXSPAM.net...
> > > > One more word of warning. If you're returning any strings longer than
> 255
> > > > characters, this won't retrieve all the text (it's limited to 255
> > > characters).
> > > >
> > > > CLR wrote:
> > > > >
> > > > > Thanks Dave.............I'll have a go at it tomorrow........I'm
> totally
> > > > > burnt out tonight..........fresh start in the morning and it will
> > > probably
> > > > > fall right in to place......just can't see the forrest for the trees
> > > > > tonight..........
> > > > >
> > > > > I do appreciate your time........this is actually quite important to
> me.
> > > > > I'm starting a new project and this feature plays a major part.....I
> > > just
> > > > > need to calm down and make it work. I'll have maybe 300 of these
> PULL's
> > > on
> > > > > each of about 50 Training Matrix Workbooks........they will actually
> be
> > > > > inside concatenated VLOOKUPs, and will draw from 500-600 individual
> > > > > employee files......and the whole thing tied together with a few
> menu's
> > > and
> > > > > a little VBA........fun for me, and I learn something new
> > > > > everyday............
> > > > >
> > > > > Many, many thanks again,
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > news:42EAC719.67F023E3@verizonXSPAM.net...
> > > > > > I was going to give you a google post:
> > > > > >
> > > > >
> > >
> http://groups.google.co.uk/group/mic...eet.functions/
> > > > > msg/e249f6c074a3adfd
> > > > > > (one line in your browser)
> > > > > >
> > > > > > But google is adding extra characters in the code and screwing it
> up.
> > > > > >
> > > > > > So I thought that a link to Harlan's FTP site would be better.
> But I
> > > just
> > > > > > looked at it and it's not up to date with what he's posted on the
> > > > > newsgroups.
> > > > > >
> > > > > > So I used the (most???) current version that I saw on google and
> tried
> > > to
> > > > > clean
> > > > > > up those google induced errors.
> > > > > >
> > > > > > I tested it to make sure it works with a call like:
> > > > > >
> > > > > > =pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
> > > > > >
> > > > > > Here's Harlan's code (but you'll need to still make that
> instrrev97
> > > > > change):
> > > > > >
> > > > > > Option Explicit
> > > > > > 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
> > > > > >
> > > > > >
> > > > > > CLR wrote:
> > > > > > >
> > > > > > > Thanks Dave.........yeah, I finally did that but still no
> > > > > joy...........it
> > > > > > > don't give error messages any more, but it also don't give
> > > > > > > results........depending on what I type in the =PULL(), I
> usually
> > > get
> > > > > > > #VALUE! or #REF!..........I've tried on both 97 and
> > > 2000.........I've
> > > > > even
> > > > > > > got both May 05 updates and still cant seem to get it to
> > > work.......I
> > > > > guess
> > > > > > > maybe it's time to start back at square one..............some
> days
> > > the
> > > > > > > Dragon wins........
> > > > > > >
> > > > > > > Vaya con Dios,
> > > > > > > Chuck, CABGx3
> > > > > > >
> > > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > > > news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > > > > > > > You'll have to do the same kind of thing.
> > > > > > > >
> > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > becomes
> > > > > > > > n = InStrRev97(expr, "!")
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > CLR wrote:
> > > > > > > > >
> > > > > > > > > Thanks Dave.........it got past that line by following your
> > > > > > > instructions, but
> > > > > > > > > now stops on
> > > > > > > > >
> > > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > >
> > > > > > > > > I tried modifying it, but no joy.....I'm just shooting in
> the
> > > dark.
> > > > > > > > >
> > > > > > > > > Vaya con Dios,
> > > > > > > > > Chuck, CABGx3
> > > > > > > > >
> > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > >
> > > > > > > > > > Instrrev was added in xl2k.
> > > > > > > > > >
> > > > > > > > > > You could create your own InstrRev97 function and use
> that:
> > > > > > > > > >
> > > > > > > > > > Function InStrRev97(mystr As Variant, mydelim As String)
> As
> > > Long
> > > > > > > > > > Dim i As Long
> > > > > > > > > > InStrRev97 = 0
> > > > > > > > > > For i = Len(mystr) To 1 Step -1
> > > > > > > > > > If Mid(mystr, i, 1) = mydelim Then
> > > > > > > > > > InStrRev97 = i
> > > > > > > > > > Exit Function
> > > > > > > > > > End If
> > > > > > > > > > Next i
> > > > > > > > > > End Function
> > > > > > > > > >
> > > > > > > > > > (Just add this to the bottom of that general module.
> > > > > > > > > >
> > > > > > > > > > so
> > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > > becomes
> > > > > > > > > > n = InStrRev97(expr, "\")
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > =====
> > > > > > > > > > There are some other functions that were added in xl2k
> (split
> > > for
> > > > > > > example). But
> > > > > > > > > > I took a cursory glance at Harlan's code and didn't see
> any
> > > others
> > > > > > > that would
> > > > > > > > > > cause you trouble.
> > > > > > > > > >
> > > > > > > > > > (Post back when you see that I missed one!)
> > > > > > > > > >
> > > > > > > > > > CLR wrote:
> > > > > > > > > > >
> > > > > > > > > > > I went after Harlan's UDF and got it but can't for the
> life
> > > of
> > > > > me
> > > > > > > figure out
> > > > > > > > > > > how to use it.
> > > > > > > > > > > I pasted it into a regular module and, all I can get
> "Sub or
> > > > > > > function not
> > > > > > > > > > > defined" on this line..
> > > > > > > > > > >
> > > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > > >
> > > > > > > > > > > Anybody see what I'm doing wrong?
> > > > > > > > > > >
> > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > >
> > > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > You'd want to use the =indirect() worksheet function.
> But
> > > > > that
> > > > > > > doesn't work
> > > > > > > > > > > > with closed files.
> > > > > > > > > > > >
> > > > > > > > > > > > Harlan Grove wrote a UDF called PULL that will
> retrieve
> > > the
> > > > > value
> > > > > > > from a closed
> > > > > > > > > > > > workbook.
> > > > > > > > > > > >
> > > > > > > > > > > > You can find the function at Harlan's FTP site:
> > > > > > > > > > > > ftp://members.aol.com/hrlngrv/
> > > > > > > > > > > >
> > > > > > > > > > > > CLR wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > Hi All........
> > > > > > > > > > > > >
> > > > > > > > > > > > > I am trying to concatenate two cells together to
> form a
> > > > > filename
> > > > > > > in a
> > > > > > > > > > > > > link............no joy, .......all I get is "That
> > > filename
> > > > > is
> > > > > > > not
> > > > > > > > > > > > > valid"........
> > > > > > > > > > > > >
> > > > > > > > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > > > > > > > >
> > > > > > > > > > > > > C6 is a name, as Jones, Fred
> > > > > > > > > > > > > B6 is a string as R1938
> > > > > > > > > > > > >
> > > > > > > > > > > > > The filename I 'm looking for is
> Jones,Fred_R1938.xls
> > > and it
> > > > > > > does
> > > > > > > > > > > > > exist........and of course works if I hard code the
> > > filename
> > > > > > > into the
> > > > > > > > > > > > > formula........
> > > > > > > > > > > > >
> > > > > > > > > > > > > =clean(c6)&"_"&b6 works fine in a test of that
> > > standalone
> > > > > part,
> > > > > > > but not in
> > > > > > > > > > > > > the link formula
> > > > > > > > > > > > >
> > > > > > > > > > > > > Any help would be appreciated........
> > > > > > > > > > > > >
> > > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > >
> > > > > > > > > > > > Dave Peterson
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > Dave Peterson
> > > > > > > > > >
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Ok, thanks for the comeback...........so it works fine for you in 2003, but
not in 2k or 97 on this machine nor on 97 at work.

I've seen various versions of Harlan's code, which had both of the types of
n = InStrRev(Len(xref), xref, "!") and
n = InStrRev(xref, "!") that you describe...........changed it in 2k and it
didn't make any difference in the way it is acting.

Did the breakpoint procedure on 2k and it stopped at the line......
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro
and gave me the #VALUE!...................does this tell you something?

Did it again in 97 and it did not stop at all, went all the way through but
still came up with the #REF

So, does this mean that the PULL function will only work in
XL2003?.........is so, maybe that's the only answer?

Vaya con Dios,
Chuck, CABGx3


"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42EC29C3.64A4DF9D@verizonXSPAM.net...
> I did the same thing you did -- I created a c:\pulltest.xls in xl2003 and
> everything worked ok.
>
>
> I think there is still one typo in Harlan's code (but it didn't affect my
test
> of your technique).
>
> This line:
>
> n = InStrRev(Len(xref), xref, "!")
> should be:
> n = InStrRev(xref, "!")
>
> I guess the next thing to try is to reopen pullmaster.xls in xl97. Change
those
> instrrev to instrrev97.
>
> Then put a break point on the first executable line in the function.
>
> This line:
> n = InStrRev97(xref, "\")
>
> Then open pulltest.xls. Select one of the offending cells and hit F2,
then
> enter.
>
> The function should start, then stop on that break point line. Then you
can F8
> through the code to find what breaks.
>
> CLR wrote:
> >
> > Ok..........for some reason the FORMAT problem seemed to come when I
copied
> > and pasted the invocation line from the Post to my workbook........don't
> > understand that, it never happened before, but I'm over it......that's
no
> > longer a problem.
> >
> > I just now opened a new workbook in XL97 . I then typed "Success, PULL
> > test" without quotes, in A1 and saved it directly to my C:\ as
PullTest.xls
> >
> > I then opened a new workbook (PullMaster.xls) and copied and pasted
Harlan's
> > code from your Post into Module1, and then copied and pasted your 97
code
> > from your Post to the bottom of that same module, and changed the two
lines
> > in Harlan's code from InStrRev to InStrRev97 and I then hand typed
this in
> > A5
> > =pull("'C:\[PullTest.xls]Sheet1'!$A$1") My result was #REF!
> >
> > I then closed PullMaster.xls and re-opened it in XL2k. Cell A5 opens
as
> > #VALUE! so I then opened Module 1 and set the InStrRev lines in
Harlan's
> > code back to their original way, and cell A5 still reads
> > #VALUE!.............
> >
> > I was just about to post this message and decided to open PullTest.xls
in
> > the background. Well, I did and A5 stayed as #VALUE! but when I
deleted
> > the equal sign, and then re-inserted it again, the PULL function worked
and
> > I got my value from PullTest.xls cell A1 that I was supposed to
> > get......only problem being is that the file I go after has to be
> > OPEN..........
> >
> > I switched back to XL97 , re-set the InStrRev lines and the same
> > thing.......I started off getting the same #REF!, instead of the #VALUE!
I
> > got in 2k, and when I opened PullTest.xls in the background, and
re=cycled
> > the Pull formulas, it all worked correctly, but only with the external
file
> > OPEN.......which is what INDIRECT does.........
> >
> > Can you see if I have done anything wrong to make it not work with
CLOSED
> > files?.........that is what I thought it was supposed to do, and what I
need
> > it to do if possible.......
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:42EBDA67.AC772A4@verizonXSPAM.net...
> > > The usual things that can cause the #name? errors:
> > >
> > > 1. You spelled the function one way in the worksheet cell and a
different
> > way
> > > in your code.
> > > 2. You put the code in the wrong spot--it belongs in a General module
> > (not
> > > behind a worksheet, not behind ThisWorkbook).
> > > 3. Maybe you put the function in a different workbook's project by
> > mistake?
> > >
> > > I'd fix that #name? error before looking for more. But functions
don't
> > bring
> > > back formats. I'd suspect that the cell was formatted (as merged)
before
> > you
> > > started.
> > >
> > >
> > >
> > > CLR wrote:
> > > >
> > > > That will not be a problem........will only be retrieving from 1-10
> > > > characters or so, but thanks for the heads-up..........how the heck
do
> > you
> > > > guys find out about stuff like that anyway?
> > > >
> > > > As for the problem...........I started from scratch with your
version of
> > > > Harlan's code and your 97 thing and all I could get was
> > #NAME?............I
> > > > went back and forth between 97 and 2k and switching the InStrRev
thing
> > back
> > > > and forth and all results were the same........#NAME?
> > > >
> > > > I even started with a new Book, and re-created the same path you
used in
> > > > your test and copied and pasted your test formula over to my book
and
> > tried
> > > > 97 and 2k with and without the InStrRev thing and with the Book2
file
> > open
> > > > and closed and it still did the same thing.........#NAME?, and you
know
> > > > what?, it comes in as a merged cell 16 columns wide, whereas the
Book2
> > cell
> > > > A1 is only one column wide.
> > > >
> > > > Obviously it's something I'm doing wrong, but I can't for the life
of me
> > see
> > > > what it might be..............maybe if you were to tell me the EXACT
> > steps
> > > > you use to test it and I could follow them and see if I get the same
> > > > results........
> > > >
> > > > Thanks for all your help,
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > news:42EAD25B.64DEA484@verizonXSPAM.net...
> > > > > One more word of warning. If you're returning any strings longer
than
> > 255
> > > > > characters, this won't retrieve all the text (it's limited to 255
> > > > characters).
> > > > >
> > > > > CLR wrote:
> > > > > >
> > > > > > Thanks Dave.............I'll have a go at it tomorrow........I'm
> > totally
> > > > > > burnt out tonight..........fresh start in the morning and it
will
> > > > probably
> > > > > > fall right in to place......just can't see the forrest for the
trees
> > > > > > tonight..........
> > > > > >
> > > > > > I do appreciate your time........this is actually quite
important to
> > me.
> > > > > > I'm starting a new project and this feature plays a major
part.....I
> > > > just
> > > > > > need to calm down and make it work. I'll have maybe 300 of
these
> > PULL's
> > > > on
> > > > > > each of about 50 Training Matrix Workbooks........they will
actually
> > be
> > > > > > inside concatenated VLOOKUPs, and will draw from 500-600
individual
> > > > > > employee files......and the whole thing tied together with a few
> > menu's
> > > > and
> > > > > > a little VBA........fun for me, and I learn something new
> > > > > > everyday............
> > > > > >
> > > > > > Many, many thanks again,
> > > > > > Vaya con Dios,
> > > > > > Chuck, CABGx3
> > > > > >
> > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > > news:42EAC719.67F023E3@verizonXSPAM.net...
> > > > > > > I was going to give you a google post:
> > > > > > >
> > > > > >
> > > >
> >
http://groups.google.co.uk/group/mic...eet.functions/
> > > > > > msg/e249f6c074a3adfd
> > > > > > > (one line in your browser)
> > > > > > >
> > > > > > > But google is adding extra characters in the code and screwing
it
> > up.
> > > > > > >
> > > > > > > So I thought that a link to Harlan's FTP site would be better.
> > But I
> > > > just
> > > > > > > looked at it and it's not up to date with what he's posted on
the
> > > > > > newsgroups.
> > > > > > >
> > > > > > > So I used the (most???) current version that I saw on google
and
> > tried
> > > > to
> > > > > > clean
> > > > > > > up those google induced errors.
> > > > > > >
> > > > > > > I tested it to make sure it works with a call like:
> > > > > > >
> > > > > > > =pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
> > > > > > >
> > > > > > > Here's Harlan's code (but you'll need to still make that
> > instrrev97
> > > > > > change):
> > > > > > >
> > > > > > > Option Explicit
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > > > CLR wrote:
> > > > > > > >
> > > > > > > > Thanks Dave.........yeah, I finally did that but still no
> > > > > > joy...........it
> > > > > > > > don't give error messages any more, but it also don't give
> > > > > > > > results........depending on what I type in the =PULL(), I
> > usually
> > > > get
> > > > > > > > #VALUE! or #REF!..........I've tried on both 97 and
> > > > 2000.........I've
> > > > > > even
> > > > > > > > got both May 05 updates and still cant seem to get it to
> > > > work.......I
> > > > > > guess
> > > > > > > > maybe it's time to start back at square
one..............some
> > days
> > > > the
> > > > > > > > Dragon wins........
> > > > > > > >
> > > > > > > > Vaya con Dios,
> > > > > > > > Chuck, CABGx3
> > > > > > > >
> > > > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > > > > news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > > > > > > > > You'll have to do the same kind of thing.
> > > > > > > > >
> > > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > > becomes
> > > > > > > > > n = InStrRev97(expr, "!")
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > CLR wrote:
> > > > > > > > > >
> > > > > > > > > > Thanks Dave.........it got past that line by following
your
> > > > > > > > instructions, but
> > > > > > > > > > now stops on
> > > > > > > > > >
> > > > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > > >
> > > > > > > > > > I tried modifying it, but no joy.....I'm just shooting
in
> > the
> > > > dark.
> > > > > > > > > >
> > > > > > > > > > Vaya con Dios,
> > > > > > > > > > Chuck, CABGx3
> > > > > > > > > >
> > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > >
> > > > > > > > > > > Instrrev was added in xl2k.
> > > > > > > > > > >
> > > > > > > > > > > You could create your own InstrRev97 function and use
> > that:
> > > > > > > > > > >
> > > > > > > > > > > Function InStrRev97(mystr As Variant, mydelim As
String)
> > As
> > > > Long
> > > > > > > > > > > Dim i As Long
> > > > > > > > > > > InStrRev97 = 0
> > > > > > > > > > > For i = Len(mystr) To 1 Step -1
> > > > > > > > > > > If Mid(mystr, i, 1) = mydelim Then
> > > > > > > > > > > InStrRev97 = i
> > > > > > > > > > > Exit Function
> > > > > > > > > > > End If
> > > > > > > > > > > Next i
> > > > > > > > > > > End Function
> > > > > > > > > > >
> > > > > > > > > > > (Just add this to the bottom of that general module.
> > > > > > > > > > >
> > > > > > > > > > > so
> > > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > > > becomes
> > > > > > > > > > > n = InStrRev97(expr, "\")
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > =====
> > > > > > > > > > > There are some other functions that were added in xl2k
> > (split
> > > > for
> > > > > > > > example). But
> > > > > > > > > > > I took a cursory glance at Harlan's code and didn't
see
> > any
> > > > others
> > > > > > > > that would
> > > > > > > > > > > cause you trouble.
> > > > > > > > > > >
> > > > > > > > > > > (Post back when you see that I missed one!)
> > > > > > > > > > >
> > > > > > > > > > > CLR wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > I went after Harlan's UDF and got it but can't for
the
> > life
> > > > of
> > > > > > me
> > > > > > > > figure out
> > > > > > > > > > > > how to use it.
> > > > > > > > > > > > I pasted it into a regular module and, all I can get
> > "Sub or
> > > > > > > > function not
> > > > > > > > > > > > defined" on this line..
> > > > > > > > > > > >
> > > > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > > > >
> > > > > > > > > > > > Anybody see what I'm doing wrong?
> > > > > > > > > > > >
> > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > >
> > > > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > > You'd want to use the =indirect() worksheet
function.
> > But
> > > > > > that
> > > > > > > > doesn't work
> > > > > > > > > > > > > with closed files.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Harlan Grove wrote a UDF called PULL that will
> > retrieve
> > > > the
> > > > > > value
> > > > > > > > from a closed
> > > > > > > > > > > > > workbook.
> > > > > > > > > > > > >
> > > > > > > > > > > > > You can find the function at Harlan's FTP site:
> > > > > > > > > > > > > ftp://members.aol.com/hrlngrv/
> > > > > > > > > > > > >
> > > > > > > > > > > > > CLR wrote:
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Hi All........
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > I am trying to concatenate two cells together to
> > form a
> > > > > > filename
> > > > > > > > in a
> > > > > > > > > > > > > > link............no joy, .......all I get is
"That
> > > > filename
> > > > > > is
> > > > > > > > not
> > > > > > > > > > > > > > valid"........
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > C6 is a name, as Jones, Fred
> > > > > > > > > > > > > > B6 is a string as R1938
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > The filename I 'm looking for is
> > Jones,Fred_R1938.xls
> > > > and it
> > > > > > > > does
> > > > > > > > > > > > > > exist........and of course works if I hard code
the
> > > > filename
> > > > > > > > into the
> > > > > > > > > > > > > > formula........
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > =clean(c6)&"_"&b6 works fine in a test of that
> > > > standalone
> > > > > > part,
> > > > > > > > but not in
> > > > > > > > > > > > > > the link formula
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Any help would be appreciated........
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > > >
> > > > > > > > > > > > > --
> > > > > > > > > > > > >
> > > > > > > > > > > > > Dave Peterson
> > > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > >
> > > > > > > > > > > Dave Peterson
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > Dave Peterson
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

I think I used Harlan's code in xl97, xl2k and xl2002, and xl2003. So I don't
think that's the trouble.

Harlan traps errors and handles them in his code.

Comment out this line:

On Error GoTo CleanUp 'immediate clean-up at this point

And see what kind of error message you get when you step through the code.

(I still don't have a guess.)

CLR wrote:
>
> Ok, thanks for the comeback...........so it works fine for you in 2003, but
> not in 2k or 97 on this machine nor on 97 at work.
>
> I've seen various versions of Harlan's code, which had both of the types of
> n = InStrRev(Len(xref), xref, "!") and
> n = InStrRev(xref, "!") that you describe...........changed it in 2k and it
> didn't make any difference in the way it is acting.
>
> Did the breakpoint procedure on 2k and it stopped at the line......
> Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro
> and gave me the #VALUE!...................does this tell you something?
>
> Did it again in 97 and it did not stop at all, went all the way through but
> still came up with the #REF
>
> So, does this mean that the PULL function will only work in
> XL2003?.........is so, maybe that's the only answer?
>
> Vaya con Dios,
> Chuck, CABGx3
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42EC29C3.64A4DF9D@verizonXSPAM.net...
> > I did the same thing you did -- I created a c:\pulltest.xls in xl2003 and
> > everything worked ok.
> >
> >
> > I think there is still one typo in Harlan's code (but it didn't affect my
> test
> > of your technique).
> >
> > This line:
> >
> > n = InStrRev(Len(xref), xref, "!")
> > should be:
> > n = InStrRev(xref, "!")
> >
> > I guess the next thing to try is to reopen pullmaster.xls in xl97. Change
> those
> > instrrev to instrrev97.
> >
> > Then put a break point on the first executable line in the function.
> >
> > This line:
> > n = InStrRev97(xref, "\")
> >
> > Then open pulltest.xls. Select one of the offending cells and hit F2,
> then
> > enter.
> >
> > The function should start, then stop on that break point line. Then you
> can F8
> > through the code to find what breaks.
> >
> > CLR wrote:
> > >
> > > Ok..........for some reason the FORMAT problem seemed to come when I
> copied
> > > and pasted the invocation line from the Post to my workbook........don't
> > > understand that, it never happened before, but I'm over it......that's
> no
> > > longer a problem.
> > >
> > > I just now opened a new workbook in XL97 . I then typed "Success, PULL
> > > test" without quotes, in A1 and saved it directly to my C:\ as
> PullTest.xls
> > >
> > > I then opened a new workbook (PullMaster.xls) and copied and pasted
> Harlan's
> > > code from your Post into Module1, and then copied and pasted your 97
> code
> > > from your Post to the bottom of that same module, and changed the two
> lines
> > > in Harlan's code from InStrRev to InStrRev97 and I then hand typed
> this in
> > > A5
> > > =pull("'C:\[PullTest.xls]Sheet1'!$A$1") My result was #REF!
> > >
> > > I then closed PullMaster.xls and re-opened it in XL2k. Cell A5 opens
> as
> > > #VALUE! so I then opened Module 1 and set the InStrRev lines in
> Harlan's
> > > code back to their original way, and cell A5 still reads
> > > #VALUE!.............
> > >
> > > I was just about to post this message and decided to open PullTest.xls
> in
> > > the background. Well, I did and A5 stayed as #VALUE! but when I
> deleted
> > > the equal sign, and then re-inserted it again, the PULL function worked
> and
> > > I got my value from PullTest.xls cell A1 that I was supposed to
> > > get......only problem being is that the file I go after has to be
> > > OPEN..........
> > >
> > > I switched back to XL97 , re-set the InStrRev lines and the same
> > > thing.......I started off getting the same #REF!, instead of the #VALUE!
> I
> > > got in 2k, and when I opened PullTest.xls in the background, and
> re=cycled
> > > the Pull formulas, it all worked correctly, but only with the external
> file
> > > OPEN.......which is what INDIRECT does.........
> > >
> > > Can you see if I have done anything wrong to make it not work with
> CLOSED
> > > files?.........that is what I thought it was supposed to do, and what I
> need
> > > it to do if possible.......
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:42EBDA67.AC772A4@verizonXSPAM.net...
> > > > The usual things that can cause the #name? errors:
> > > >
> > > > 1. You spelled the function one way in the worksheet cell and a
> different
> > > way
> > > > in your code.
> > > > 2. You put the code in the wrong spot--it belongs in a General module
> > > (not
> > > > behind a worksheet, not behind ThisWorkbook).
> > > > 3. Maybe you put the function in a different workbook's project by
> > > mistake?
> > > >
> > > > I'd fix that #name? error before looking for more. But functions
> don't
> > > bring
> > > > back formats. I'd suspect that the cell was formatted (as merged)
> before
> > > you
> > > > started.
> > > >
> > > >
> > > >
> > > > CLR wrote:
> > > > >
> > > > > That will not be a problem........will only be retrieving from 1-10
> > > > > characters or so, but thanks for the heads-up..........how the heck
> do
> > > you
> > > > > guys find out about stuff like that anyway?
> > > > >
> > > > > As for the problem...........I started from scratch with your
> version of
> > > > > Harlan's code and your 97 thing and all I could get was
> > > #NAME?............I
> > > > > went back and forth between 97 and 2k and switching the InStrRev
> thing
> > > back
> > > > > and forth and all results were the same........#NAME?
> > > > >
> > > > > I even started with a new Book, and re-created the same path you
> used in
> > > > > your test and copied and pasted your test formula over to my book
> and
> > > tried
> > > > > 97 and 2k with and without the InStrRev thing and with the Book2
> file
> > > open
> > > > > and closed and it still did the same thing.........#NAME?, and you
> know
> > > > > what?, it comes in as a merged cell 16 columns wide, whereas the
> Book2
> > > cell
> > > > > A1 is only one column wide.
> > > > >
> > > > > Obviously it's something I'm doing wrong, but I can't for the life
> of me
> > > see
> > > > > what it might be..............maybe if you were to tell me the EXACT
> > > steps
> > > > > you use to test it and I could follow them and see if I get the same
> > > > > results........
> > > > >
> > > > > Thanks for all your help,
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > news:42EAD25B.64DEA484@verizonXSPAM.net...
> > > > > > One more word of warning. If you're returning any strings longer
> than
> > > 255
> > > > > > characters, this won't retrieve all the text (it's limited to 255
> > > > > characters).
> > > > > >
> > > > > > CLR wrote:
> > > > > > >
> > > > > > > Thanks Dave.............I'll have a go at it tomorrow........I'm
> > > totally
> > > > > > > burnt out tonight..........fresh start in the morning and it
> will
> > > > > probably
> > > > > > > fall right in to place......just can't see the forrest for the
> trees
> > > > > > > tonight..........
> > > > > > >
> > > > > > > I do appreciate your time........this is actually quite
> important to
> > > me.
> > > > > > > I'm starting a new project and this feature plays a major
> part.....I
> > > > > just
> > > > > > > need to calm down and make it work. I'll have maybe 300 of
> these
> > > PULL's
> > > > > on
> > > > > > > each of about 50 Training Matrix Workbooks........they will
> actually
> > > be
> > > > > > > inside concatenated VLOOKUPs, and will draw from 500-600
> individual
> > > > > > > employee files......and the whole thing tied together with a few
> > > menu's
> > > > > and
> > > > > > > a little VBA........fun for me, and I learn something new
> > > > > > > everyday............
> > > > > > >
> > > > > > > Many, many thanks again,
> > > > > > > Vaya con Dios,
> > > > > > > Chuck, CABGx3
> > > > > > >
> > > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > > > news:42EAC719.67F023E3@verizonXSPAM.net...
> > > > > > > > I was going to give you a google post:
> > > > > > > >
> > > > > > >
> > > > >
> > >
> http://groups.google.co.uk/group/mic...eet.functions/
> > > > > > > msg/e249f6c074a3adfd
> > > > > > > > (one line in your browser)
> > > > > > > >
> > > > > > > > But google is adding extra characters in the code and screwing
> it
> > > up.
> > > > > > > >
> > > > > > > > So I thought that a link to Harlan's FTP site would be better.
> > > But I
> > > > > just
> > > > > > > > looked at it and it's not up to date with what he's posted on
> the
> > > > > > > newsgroups.
> > > > > > > >
> > > > > > > > So I used the (most???) current version that I saw on google
> and
> > > tried
> > > > > to
> > > > > > > clean
> > > > > > > > up those google induced errors.
> > > > > > > >
> > > > > > > > I tested it to make sure it works with a call like:
> > > > > > > >
> > > > > > > > =pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
> > > > > > > >
> > > > > > > > Here's Harlan's code (but you'll need to still make that
> > > instrrev97
> > > > > > > change):
> > > > > > > >
> > > > > > > > Option Explicit
> > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > > > CLR wrote:
> > > > > > > > >
> > > > > > > > > Thanks Dave.........yeah, I finally did that but still no
> > > > > > > joy...........it
> > > > > > > > > don't give error messages any more, but it also don't give
> > > > > > > > > results........depending on what I type in the =PULL(), I
> > > usually
> > > > > get
> > > > > > > > > #VALUE! or #REF!..........I've tried on both 97 and
> > > > > 2000.........I've
> > > > > > > even
> > > > > > > > > got both May 05 updates and still cant seem to get it to
> > > > > work.......I
> > > > > > > guess
> > > > > > > > > maybe it's time to start back at square
> one..............some
> > > days
> > > > > the
> > > > > > > > > Dragon wins........
> > > > > > > > >
> > > > > > > > > Vaya con Dios,
> > > > > > > > > Chuck, CABGx3
> > > > > > > > >
> > > > > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > > > > > news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > > > > > > > > > You'll have to do the same kind of thing.
> > > > > > > > > >
> > > > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > > > becomes
> > > > > > > > > > n = InStrRev97(expr, "!")
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > CLR wrote:
> > > > > > > > > > >
> > > > > > > > > > > Thanks Dave.........it got past that line by following
> your
> > > > > > > > > instructions, but
> > > > > > > > > > > now stops on
> > > > > > > > > > >
> > > > > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > > > >
> > > > > > > > > > > I tried modifying it, but no joy.....I'm just shooting
> in
> > > the
> > > > > dark.
> > > > > > > > > > >
> > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > >
> > > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > > >
> > > > > > > > > > > > Instrrev was added in xl2k.
> > > > > > > > > > > >
> > > > > > > > > > > > You could create your own InstrRev97 function and use
> > > that:
> > > > > > > > > > > >
> > > > > > > > > > > > Function InStrRev97(mystr As Variant, mydelim As
> String)
> > > As
> > > > > Long
> > > > > > > > > > > > Dim i As Long
> > > > > > > > > > > > InStrRev97 = 0
> > > > > > > > > > > > For i = Len(mystr) To 1 Step -1
> > > > > > > > > > > > If Mid(mystr, i, 1) = mydelim Then
> > > > > > > > > > > > InStrRev97 = i
> > > > > > > > > > > > Exit Function
> > > > > > > > > > > > End If
> > > > > > > > > > > > Next i
> > > > > > > > > > > > End Function
> > > > > > > > > > > >
> > > > > > > > > > > > (Just add this to the bottom of that general module.
> > > > > > > > > > > >
> > > > > > > > > > > > so
> > > > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > > > > becomes
> > > > > > > > > > > > n = InStrRev97(expr, "\")
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > =====
> > > > > > > > > > > > There are some other functions that were added in xl2k
> > > (split
> > > > > for
> > > > > > > > > example). But
> > > > > > > > > > > > I took a cursory glance at Harlan's code and didn't
> see
> > > any
> > > > > others
> > > > > > > > > that would
> > > > > > > > > > > > cause you trouble.
> > > > > > > > > > > >
> > > > > > > > > > > > (Post back when you see that I missed one!)
> > > > > > > > > > > >
> > > > > > > > > > > > CLR wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > I went after Harlan's UDF and got it but can't for
> the
> > > life
> > > > > of
> > > > > > > me
> > > > > > > > > figure out
> > > > > > > > > > > > > how to use it.
> > > > > > > > > > > > > I pasted it into a regular module and, all I can get
> > > "Sub or
> > > > > > > > > function not
> > > > > > > > > > > > > defined" on this line..
> > > > > > > > > > > > >
> > > > > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > > > > >
> > > > > > > > > > > > > Anybody see what I'm doing wrong?
> > > > > > > > > > > > >
> > > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > > >
> > > > > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > > You'd want to use the =indirect() worksheet
> function.
> > > But
> > > > > > > that
> > > > > > > > > doesn't work
> > > > > > > > > > > > > > with closed files.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Harlan Grove wrote a UDF called PULL that will
> > > retrieve
> > > > > the
> > > > > > > value
> > > > > > > > > from a closed
> > > > > > > > > > > > > > workbook.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > You can find the function at Harlan's FTP site:
> > > > > > > > > > > > > > ftp://members.aol.com/hrlngrv/
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > CLR wrote:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Hi All........
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > I am trying to concatenate two cells together to
> > > form a
> > > > > > > filename
> > > > > > > > > in a
> > > > > > > > > > > > > > > link............no joy, .......all I get is
> "That
> > > > > filename
> > > > > > > is
> > > > > > > > > not
> > > > > > > > > > > > > > > valid"........
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > C6 is a name, as Jones, Fred
> > > > > > > > > > > > > > > B6 is a string as R1938
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > The filename I 'm looking for is
> > > Jones,Fred_R1938.xls
> > > > > and it
> > > > > > > > > does
> > > > > > > > > > > > > > > exist........and of course works if I hard code
> the
> > > > > filename
> > > > > > > > > into the
> > > > > > > > > > > > > > > formula........
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > =clean(c6)&"_"&b6 works fine in a test of that
> > > > > standalone
> > > > > > > part,
> > > > > > > > > but not in
> > > > > > > > > > > > > > > the link formula
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Any help would be appreciated........
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > --
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Dave Peterson
> > > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > >
> > > > > > > > > > > > Dave Peterson
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > Dave Peterson
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

No Error message after commenting out that line........

It stopped on
Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro

Cell still returns #VALUE!, but works fine if I open PullTest.xls

I even tried going to Harlan's FTP site and downloading directly again, but
into xl2k this time and I still get the #VALUE! but this version doesn't
even work if I open the test file.........

What to do, what to do?
I'm open to try any suggestions.............

May I send you my PullMaster.xls and PullTest.xls directly and see if they
will work on your 2003?.........or please send me your two that DO work
there and I can check them here on my installations of 2k and 97......

Will xl2003 work on WinMe or does it need WinXP?

Vaya con Dios,
Chuck, CABGx3




"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:42ECC0CC.70186F76@verizonXSPAM.net...
> I think I used Harlan's code in xl97, xl2k and xl2002, and xl2003. So I
don't
> think that's the trouble.
>
> Harlan traps errors and handles them in his code.
>
> Comment out this line:
>
> On Error GoTo CleanUp 'immediate clean-up at this point
>
> And see what kind of error message you get when you step through the code.
>
> (I still don't have a guess.)
>
> CLR wrote:
> >
> > Ok, thanks for the comeback...........so it works fine for you in 2003,
but
> > not in 2k or 97 on this machine nor on 97 at work.
> >
> > I've seen various versions of Harlan's code, which had both of the types
of
> > n = InStrRev(Len(xref), xref, "!") and
> > n = InStrRev(xref, "!") that you describe...........changed it in 2k and
it
> > didn't make any difference in the way it is acting.
> >
> > Did the breakpoint procedure on 2k and it stopped at the line......
> > Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro
> > and gave me the #VALUE!...................does this tell you something?
> >
> > Did it again in 97 and it did not stop at all, went all the way through
but
> > still came up with the #REF
> >
> > So, does this mean that the PULL function will only work in
> > XL2003?.........is so, maybe that's the only answer?
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > news:42EC29C3.64A4DF9D@verizonXSPAM.net...
> > > I did the same thing you did -- I created a c:\pulltest.xls in xl2003
and
> > > everything worked ok.
> > >
> > >
> > > I think there is still one typo in Harlan's code (but it didn't affect
my
> > test
> > > of your technique).
> > >
> > > This line:
> > >
> > > n = InStrRev(Len(xref), xref, "!")
> > > should be:
> > > n = InStrRev(xref, "!")
> > >
> > > I guess the next thing to try is to reopen pullmaster.xls in xl97.
Change
> > those
> > > instrrev to instrrev97.
> > >
> > > Then put a break point on the first executable line in the function.
> > >
> > > This line:
> > > n = InStrRev97(xref, "\")
> > >
> > > Then open pulltest.xls. Select one of the offending cells and hit F2,
> > then
> > > enter.
> > >
> > > The function should start, then stop on that break point line. Then
you
> > can F8
> > > through the code to find what breaks.
> > >
> > > CLR wrote:
> > > >
> > > > Ok..........for some reason the FORMAT problem seemed to come when I
> > copied
> > > > and pasted the invocation line from the Post to my
workbook........don't
> > > > understand that, it never happened before, but I'm over
it......that's
> > no
> > > > longer a problem.
> > > >
> > > > I just now opened a new workbook in XL97 . I then typed "Success,
PULL
> > > > test" without quotes, in A1 and saved it directly to my C:\ as
> > PullTest.xls
> > > >
> > > > I then opened a new workbook (PullMaster.xls) and copied and pasted
> > Harlan's
> > > > code from your Post into Module1, and then copied and pasted your 97
> > code
> > > > from your Post to the bottom of that same module, and changed the
two
> > lines
> > > > in Harlan's code from InStrRev to InStrRev97 and I then hand typed
> > this in
> > > > A5
> > > > =pull("'C:\[PullTest.xls]Sheet1'!$A$1") My result was #REF!
> > > >
> > > > I then closed PullMaster.xls and re-opened it in XL2k. Cell A5
opens
> > as
> > > > #VALUE! so I then opened Module 1 and set the InStrRev lines in
> > Harlan's
> > > > code back to their original way, and cell A5 still reads
> > > > #VALUE!.............
> > > >
> > > > I was just about to post this message and decided to open
PullTest.xls
> > in
> > > > the background. Well, I did and A5 stayed as #VALUE! but when I
> > deleted
> > > > the equal sign, and then re-inserted it again, the PULL function
worked
> > and
> > > > I got my value from PullTest.xls cell A1 that I was supposed to
> > > > get......only problem being is that the file I go after has to be
> > > > OPEN..........
> > > >
> > > > I switched back to XL97 , re-set the InStrRev lines and the same
> > > > thing.......I started off getting the same #REF!, instead of the
#VALUE!
> > I
> > > > got in 2k, and when I opened PullTest.xls in the background, and
> > re=cycled
> > > > the Pull formulas, it all worked correctly, but only with the
external
> > file
> > > > OPEN.......which is what INDIRECT does.........
> > > >
> > > > Can you see if I have done anything wrong to make it not work with
> > CLOSED
> > > > files?.........that is what I thought it was supposed to do, and
what I
> > need
> > > > it to do if possible.......
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > news:42EBDA67.AC772A4@verizonXSPAM.net...
> > > > > The usual things that can cause the #name? errors:
> > > > >
> > > > > 1. You spelled the function one way in the worksheet cell and a
> > different
> > > > way
> > > > > in your code.
> > > > > 2. You put the code in the wrong spot--it belongs in a General
module
> > > > (not
> > > > > behind a worksheet, not behind ThisWorkbook).
> > > > > 3. Maybe you put the function in a different workbook's project
by
> > > > mistake?
> > > > >
> > > > > I'd fix that #name? error before looking for more. But functions
> > don't
> > > > bring
> > > > > back formats. I'd suspect that the cell was formatted (as merged)
> > before
> > > > you
> > > > > started.
> > > > >
> > > > >
> > > > >
> > > > > CLR wrote:
> > > > > >
> > > > > > That will not be a problem........will only be retrieving from
1-10
> > > > > > characters or so, but thanks for the heads-up..........how the
heck
> > do
> > > > you
> > > > > > guys find out about stuff like that anyway?
> > > > > >
> > > > > > As for the problem...........I started from scratch with your
> > version of
> > > > > > Harlan's code and your 97 thing and all I could get was
> > > > #NAME?............I
> > > > > > went back and forth between 97 and 2k and switching the InStrRev
> > thing
> > > > back
> > > > > > and forth and all results were the same........#NAME?
> > > > > >
> > > > > > I even started with a new Book, and re-created the same path you
> > used in
> > > > > > your test and copied and pasted your test formula over to my
book
> > and
> > > > tried
> > > > > > 97 and 2k with and without the InStrRev thing and with the Book2
> > file
> > > > open
> > > > > > and closed and it still did the same thing.........#NAME?, and
you
> > know
> > > > > > what?, it comes in as a merged cell 16 columns wide, whereas the
> > Book2
> > > > cell
> > > > > > A1 is only one column wide.
> > > > > >
> > > > > > Obviously it's something I'm doing wrong, but I can't for the
life
> > of me
> > > > see
> > > > > > what it might be..............maybe if you were to tell me the
EXACT
> > > > steps
> > > > > > you use to test it and I could follow them and see if I get the
same
> > > > > > results........
> > > > > >
> > > > > > Thanks for all your help,
> > > > > > Vaya con Dios,
> > > > > > Chuck, CABGx3
> > > > > >
> > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > > news:42EAD25B.64DEA484@verizonXSPAM.net...
> > > > > > > One more word of warning. If you're returning any strings
longer
> > than
> > > > 255
> > > > > > > characters, this won't retrieve all the text (it's limited to
255
> > > > > > characters).
> > > > > > >
> > > > > > > CLR wrote:
> > > > > > > >
> > > > > > > > Thanks Dave.............I'll have a go at it
tomorrow........I'm
> > > > totally
> > > > > > > > burnt out tonight..........fresh start in the morning and it
> > will
> > > > > > probably
> > > > > > > > fall right in to place......just can't see the forrest for
the
> > trees
> > > > > > > > tonight..........
> > > > > > > >
> > > > > > > > I do appreciate your time........this is actually quite
> > important to
> > > > me.
> > > > > > > > I'm starting a new project and this feature plays a major
> > part.....I
> > > > > > just
> > > > > > > > need to calm down and make it work. I'll have maybe 300 of
> > these
> > > > PULL's
> > > > > > on
> > > > > > > > each of about 50 Training Matrix Workbooks........they will
> > actually
> > > > be
> > > > > > > > inside concatenated VLOOKUPs, and will draw from 500-600
> > individual
> > > > > > > > employee files......and the whole thing tied together with a
few
> > > > menu's
> > > > > > and
> > > > > > > > a little VBA........fun for me, and I learn something new
> > > > > > > > everyday............
> > > > > > > >
> > > > > > > > Many, many thanks again,
> > > > > > > > Vaya con Dios,
> > > > > > > > Chuck, CABGx3
> > > > > > > >
> > > > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > > > > news:42EAC719.67F023E3@verizonXSPAM.net...
> > > > > > > > > I was going to give you a google post:
> > > > > > > > >
> > > > > > > >
> > > > > >
> > > >
> >
http://groups.google.co.uk/group/mic...eet.functions/
> > > > > > > > msg/e249f6c074a3adfd
> > > > > > > > > (one line in your browser)
> > > > > > > > >
> > > > > > > > > But google is adding extra characters in the code and
screwing
> > it
> > > > up.
> > > > > > > > >
> > > > > > > > > So I thought that a link to Harlan's FTP site would be
better.
> > > > But I
> > > > > > just
> > > > > > > > > looked at it and it's not up to date with what he's posted
on
> > the
> > > > > > > > newsgroups.
> > > > > > > > >
> > > > > > > > > So I used the (most???) current version that I saw on
google
> > and
> > > > tried
> > > > > > to
> > > > > > > > clean
> > > > > > > > > up those google induced errors.
> > > > > > > > >
> > > > > > > > > I tested it to make sure it works with a call like:
> > > > > > > > >
> > > > > > > > > =pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
> > > > > > > > >
> > > > > > > > > Here's Harlan's code (but you'll need to still make that
> > > > instrrev97
> > > > > > > > change):
> > > > > > > > >
> > > > > > > > > Option Explicit
> > > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > CLR wrote:
> > > > > > > > > >
> > > > > > > > > > Thanks Dave.........yeah, I finally did that but still
no
> > > > > > > > joy...........it
> > > > > > > > > > don't give error messages any more, but it also don't
give
> > > > > > > > > > results........depending on what I type in the =PULL(),
I
> > > > usually
> > > > > > get
> > > > > > > > > > #VALUE! or #REF!..........I've tried on both 97 and
> > > > > > 2000.........I've
> > > > > > > > even
> > > > > > > > > > got both May 05 updates and still cant seem to get it to
> > > > > > work.......I
> > > > > > > > guess
> > > > > > > > > > maybe it's time to start back at square
> > one..............some
> > > > days
> > > > > > the
> > > > > > > > > > Dragon wins........
> > > > > > > > > >
> > > > > > > > > > Vaya con Dios,
> > > > > > > > > > Chuck, CABGx3
> > > > > > > > > >
> > > > > > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in
message
> > > > > > > > > > news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > > > > > > > > > > You'll have to do the same kind of thing.
> > > > > > > > > > >
> > > > > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > > > > becomes
> > > > > > > > > > > n = InStrRev97(expr, "!")
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > CLR wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > Thanks Dave.........it got past that line by
following
> > your
> > > > > > > > > > instructions, but
> > > > > > > > > > > > now stops on
> > > > > > > > > > > >
> > > > > > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > > > > >
> > > > > > > > > > > > I tried modifying it, but no joy.....I'm just
shooting
> > in
> > > > the
> > > > > > dark.
> > > > > > > > > > > >
> > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > >
> > > > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > > Instrrev was added in xl2k.
> > > > > > > > > > > > >
> > > > > > > > > > > > > You could create your own InstrRev97 function and
use
> > > > that:
> > > > > > > > > > > > >
> > > > > > > > > > > > > Function InStrRev97(mystr As Variant, mydelim As
> > String)
> > > > As
> > > > > > Long
> > > > > > > > > > > > > Dim i As Long
> > > > > > > > > > > > > InStrRev97 = 0
> > > > > > > > > > > > > For i = Len(mystr) To 1 Step -1
> > > > > > > > > > > > > If Mid(mystr, i, 1) = mydelim Then
> > > > > > > > > > > > > InStrRev97 = i
> > > > > > > > > > > > > Exit Function
> > > > > > > > > > > > > End If
> > > > > > > > > > > > > Next i
> > > > > > > > > > > > > End Function
> > > > > > > > > > > > >
> > > > > > > > > > > > > (Just add this to the bottom of that general
module.
> > > > > > > > > > > > >
> > > > > > > > > > > > > so
> > > > > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > > > > > becomes
> > > > > > > > > > > > > n = InStrRev97(expr, "\")
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > =====
> > > > > > > > > > > > > There are some other functions that were added in
xl2k
> > > > (split
> > > > > > for
> > > > > > > > > > example). But
> > > > > > > > > > > > > I took a cursory glance at Harlan's code and
didn't
> > see
> > > > any
> > > > > > others
> > > > > > > > > > that would
> > > > > > > > > > > > > cause you trouble.
> > > > > > > > > > > > >
> > > > > > > > > > > > > (Post back when you see that I missed one!)
> > > > > > > > > > > > >
> > > > > > > > > > > > > CLR wrote:
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > I went after Harlan's UDF and got it but can't
for
> > the
> > > > life
> > > > > > of
> > > > > > > > me
> > > > > > > > > > figure out
> > > > > > > > > > > > > > how to use it.
> > > > > > > > > > > > > > I pasted it into a regular module and, all I can
get
> > > > "Sub or
> > > > > > > > > > function not
> > > > > > > > > > > > > > defined" on this line..
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Anybody see what I'm doing wrong?
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > > You'd want to use the =indirect() worksheet
> > function.
> > > > But
> > > > > > > > that
> > > > > > > > > > doesn't work
> > > > > > > > > > > > > > > with closed files.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Harlan Grove wrote a UDF called PULL that will
> > > > retrieve
> > > > > > the
> > > > > > > > value
> > > > > > > > > > from a closed
> > > > > > > > > > > > > > > workbook.
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > You can find the function at Harlan's FTP
site:
> > > > > > > > > > > > > > > ftp://members.aol.com/hrlngrv/
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > CLR wrote:
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Hi All........
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > I am trying to concatenate two cells
together to
> > > > form a
> > > > > > > > filename
> > > > > > > > > > in a
> > > > > > > > > > > > > > > > link............no joy, .......all I get is
> > "That
> > > > > > filename
> > > > > > > > is
> > > > > > > > > > not
> > > > > > > > > > > > > > > > valid"........
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > C6 is a name, as Jones, Fred
> > > > > > > > > > > > > > > > B6 is a string as R1938
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > The filename I 'm looking for is
> > > > Jones,Fred_R1938.xls
> > > > > > and it
> > > > > > > > > > does
> > > > > > > > > > > > > > > > exist........and of course works if I hard
code
> > the
> > > > > > filename
> > > > > > > > > > into the
> > > > > > > > > > > > > > > > formula........
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > =clean(c6)&"_"&b6 works fine in a test of
that
> > > > > > standalone
> > > > > > > > part,
> > > > > > > > > > but not in
> > > > > > > > > > > > > > > > the link formula
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Any help would be appreciated........
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > --
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Dave Peterson
> > > > > > > > > > > > > > >
> > > > > > > > > > > > >
> > > > > > > > > > > > > --
> > > > > > > > > > > > >
> > > > > > > > > > > > > Dave Peterson
> > > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > --
> > > > > > > > > > >
> > > > > > > > > > > Dave Peterson
> > > > > > > > >
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > > Dave Peterson
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

Sorry Chuck, No help here from me ... JUST A Comment!

Just tripping over this thread for the first time, and starting from the
beginning to read it.

It's a pleasure to be able to go from post to post with a mouse click, and
start reading each one.

LONG LIVE TOP POSTING! ! !<vbg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"CLR" <croberts@tampabay.rr.com> wrote in message
news:eNwwWedlFHA.2860@TK2MSFTNGP15.phx.gbl...
> No Error message after commenting out that line........
>
> It stopped on
> Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro
>
> Cell still returns #VALUE!, but works fine if I open PullTest.xls
>
> I even tried going to Harlan's FTP site and downloading directly again,
but
> into xl2k this time and I still get the #VALUE! but this version doesn't
> even work if I open the test file.........
>
> What to do, what to do?
> I'm open to try any suggestions.............
>
> May I send you my PullMaster.xls and PullTest.xls directly and see if they
> will work on your 2003?.........or please send me your two that DO work
> there and I can check them here on my installations of 2k and 97......
>
> Will xl2003 work on WinMe or does it need WinXP?
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>
> "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> news:42ECC0CC.70186F76@verizonXSPAM.net...
> > I think I used Harlan's code in xl97, xl2k and xl2002, and xl2003. So I
> don't
> > think that's the trouble.
> >
> > Harlan traps errors and handles them in his code.
> >
> > Comment out this line:
> >
> > On Error GoTo CleanUp 'immediate clean-up at this point
> >
> > And see what kind of error message you get when you step through the
code.
> >
> > (I still don't have a guess.)
> >
> > CLR wrote:
> > >
> > > Ok, thanks for the comeback...........so it works fine for you in
2003,
> but
> > > not in 2k or 97 on this machine nor on 97 at work.
> > >
> > > I've seen various versions of Harlan's code, which had both of the
types
> of
> > > n = InStrRev(Len(xref), xref, "!") and
> > > n = InStrRev(xref, "!") that you describe...........changed it in 2k
and
> it
> > > didn't make any difference in the way it is acting.
> > >
> > > Did the breakpoint procedure on 2k and it stopped at the line......
> > > Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro
> > > and gave me the #VALUE!...................does this tell you
something?
> > >
> > > Did it again in 97 and it did not stop at all, went all the way
through
> but
> > > still came up with the #REF
> > >
> > > So, does this mean that the PULL function will only work in
> > > XL2003?.........is so, maybe that's the only answer?
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > news:42EC29C3.64A4DF9D@verizonXSPAM.net...
> > > > I did the same thing you did -- I created a c:\pulltest.xls in
xl2003
> and
> > > > everything worked ok.
> > > >
> > > >
> > > > I think there is still one typo in Harlan's code (but it didn't
affect
> my
> > > test
> > > > of your technique).
> > > >
> > > > This line:
> > > >
> > > > n = InStrRev(Len(xref), xref, "!")
> > > > should be:
> > > > n = InStrRev(xref, "!")
> > > >
> > > > I guess the next thing to try is to reopen pullmaster.xls in xl97.
> Change
> > > those
> > > > instrrev to instrrev97.
> > > >
> > > > Then put a break point on the first executable line in the function.
> > > >
> > > > This line:
> > > > n = InStrRev97(xref, "\")
> > > >
> > > > Then open pulltest.xls. Select one of the offending cells and hit
F2,
> > > then
> > > > enter.
> > > >
> > > > The function should start, then stop on that break point line. Then
> you
> > > can F8
> > > > through the code to find what breaks.
> > > >
> > > > CLR wrote:
> > > > >
> > > > > Ok..........for some reason the FORMAT problem seemed to come when
I
> > > copied
> > > > > and pasted the invocation line from the Post to my
> workbook........don't
> > > > > understand that, it never happened before, but I'm over
> it......that's
> > > no
> > > > > longer a problem.
> > > > >
> > > > > I just now opened a new workbook in XL97 . I then typed "Success,
> PULL
> > > > > test" without quotes, in A1 and saved it directly to my C:\ as
> > > PullTest.xls
> > > > >
> > > > > I then opened a new workbook (PullMaster.xls) and copied and
pasted
> > > Harlan's
> > > > > code from your Post into Module1, and then copied and pasted your
97
> > > code
> > > > > from your Post to the bottom of that same module, and changed the
> two
> > > lines
> > > > > in Harlan's code from InStrRev to InStrRev97 and I then hand
typed
> > > this in
> > > > > A5
> > > > > =pull("'C:\[PullTest.xls]Sheet1'!$A$1") My result was #REF!
> > > > >
> > > > > I then closed PullMaster.xls and re-opened it in XL2k. Cell A5
> opens
> > > as
> > > > > #VALUE! so I then opened Module 1 and set the InStrRev lines in
> > > Harlan's
> > > > > code back to their original way, and cell A5 still reads
> > > > > #VALUE!.............
> > > > >
> > > > > I was just about to post this message and decided to open
> PullTest.xls
> > > in
> > > > > the background. Well, I did and A5 stayed as #VALUE! but when I
> > > deleted
> > > > > the equal sign, and then re-inserted it again, the PULL function
> worked
> > > and
> > > > > I got my value from PullTest.xls cell A1 that I was supposed to
> > > > > get......only problem being is that the file I go after has to be
> > > > > OPEN..........
> > > > >
> > > > > I switched back to XL97 , re-set the InStrRev lines and the same
> > > > > thing.......I started off getting the same #REF!, instead of the
> #VALUE!
> > > I
> > > > > got in 2k, and when I opened PullTest.xls in the background, and
> > > re=cycled
> > > > > the Pull formulas, it all worked correctly, but only with the
> external
> > > file
> > > > > OPEN.......which is what INDIRECT does.........
> > > > >
> > > > > Can you see if I have done anything wrong to make it not work with
> > > CLOSED
> > > > > files?.........that is what I thought it was supposed to do, and
> what I
> > > need
> > > > > it to do if possible.......
> > > > >
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > news:42EBDA67.AC772A4@verizonXSPAM.net...
> > > > > > The usual things that can cause the #name? errors:
> > > > > >
> > > > > > 1. You spelled the function one way in the worksheet cell and a
> > > different
> > > > > way
> > > > > > in your code.
> > > > > > 2. You put the code in the wrong spot--it belongs in a General
> module
> > > > > (not
> > > > > > behind a worksheet, not behind ThisWorkbook).
> > > > > > 3. Maybe you put the function in a different workbook's project
> by
> > > > > mistake?
> > > > > >
> > > > > > I'd fix that #name? error before looking for more. But
functions
> > > don't
> > > > > bring
> > > > > > back formats. I'd suspect that the cell was formatted (as
merged)
> > > before
> > > > > you
> > > > > > started.
> > > > > >
> > > > > >
> > > > > >
> > > > > > CLR wrote:
> > > > > > >
> > > > > > > That will not be a problem........will only be retrieving from
> 1-10
> > > > > > > characters or so, but thanks for the heads-up..........how the
> heck
> > > do
> > > > > you
> > > > > > > guys find out about stuff like that anyway?
> > > > > > >
> > > > > > > As for the problem...........I started from scratch with your
> > > version of
> > > > > > > Harlan's code and your 97 thing and all I could get was
> > > > > #NAME?............I
> > > > > > > went back and forth between 97 and 2k and switching the
InStrRev
> > > thing
> > > > > back
> > > > > > > and forth and all results were the same........#NAME?
> > > > > > >
> > > > > > > I even started with a new Book, and re-created the same path
you
> > > used in
> > > > > > > your test and copied and pasted your test formula over to my
> book
> > > and
> > > > > tried
> > > > > > > 97 and 2k with and without the InStrRev thing and with the
Book2
> > > file
> > > > > open
> > > > > > > and closed and it still did the same thing.........#NAME?, and
> you
> > > know
> > > > > > > what?, it comes in as a merged cell 16 columns wide, whereas
the
> > > Book2
> > > > > cell
> > > > > > > A1 is only one column wide.
> > > > > > >
> > > > > > > Obviously it's something I'm doing wrong, but I can't for the
> life
> > > of me
> > > > > see
> > > > > > > what it might be..............maybe if you were to tell me the
> EXACT
> > > > > steps
> > > > > > > you use to test it and I could follow them and see if I get
the
> same
> > > > > > > results........
> > > > > > >
> > > > > > > Thanks for all your help,
> > > > > > > Vaya con Dios,
> > > > > > > Chuck, CABGx3
> > > > > > >
> > > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
> > > > > > > news:42EAD25B.64DEA484@verizonXSPAM.net...
> > > > > > > > One more word of warning. If you're returning any strings
> longer
> > > than
> > > > > 255
> > > > > > > > characters, this won't retrieve all the text (it's limited
to
> 255
> > > > > > > characters).
> > > > > > > >
> > > > > > > > CLR wrote:
> > > > > > > > >
> > > > > > > > > Thanks Dave.............I'll have a go at it
> tomorrow........I'm
> > > > > totally
> > > > > > > > > burnt out tonight..........fresh start in the morning and
it
> > > will
> > > > > > > probably
> > > > > > > > > fall right in to place......just can't see the forrest for
> the
> > > trees
> > > > > > > > > tonight..........
> > > > > > > > >
> > > > > > > > > I do appreciate your time........this is actually quite
> > > important to
> > > > > me.
> > > > > > > > > I'm starting a new project and this feature plays a major
> > > part.....I
> > > > > > > just
> > > > > > > > > need to calm down and make it work. I'll have maybe 300
of
> > > these
> > > > > PULL's
> > > > > > > on
> > > > > > > > > each of about 50 Training Matrix Workbooks........they
will
> > > actually
> > > > > be
> > > > > > > > > inside concatenated VLOOKUPs, and will draw from 500-600
> > > individual
> > > > > > > > > employee files......and the whole thing tied together with
a
> few
> > > > > menu's
> > > > > > > and
> > > > > > > > > a little VBA........fun for me, and I learn something new
> > > > > > > > > everyday............
> > > > > > > > >
> > > > > > > > > Many, many thanks again,
> > > > > > > > > Vaya con Dios,
> > > > > > > > > Chuck, CABGx3
> > > > > > > > >
> > > > > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in
message
> > > > > > > > > news:42EAC719.67F023E3@verizonXSPAM.net...
> > > > > > > > > > I was going to give you a google post:
> > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > >
> > >
>
http://groups.google.co.uk/group/mic...eet.functions/
> > > > > > > > > msg/e249f6c074a3adfd
> > > > > > > > > > (one line in your browser)
> > > > > > > > > >
> > > > > > > > > > But google is adding extra characters in the code and
> screwing
> > > it
> > > > > up.
> > > > > > > > > >
> > > > > > > > > > So I thought that a link to Harlan's FTP site would be
> better.
> > > > > But I
> > > > > > > just
> > > > > > > > > > looked at it and it's not up to date with what he's
posted
> on
> > > the
> > > > > > > > > newsgroups.
> > > > > > > > > >
> > > > > > > > > > So I used the (most???) current version that I saw on
> google
> > > and
> > > > > tried
> > > > > > > to
> > > > > > > > > clean
> > > > > > > > > > up those google induced errors.
> > > > > > > > > >
> > > > > > > > > > I tested it to make sure it works with a call like:
> > > > > > > > > >
> > > > > > > > > > =pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")
> > > > > > > > > >
> > > > > > > > > > Here's Harlan's code (but you'll need to still make that
> > > > > instrrev97
> > > > > > > > > change):
> > > > > > > > > >
> > > > > > > > > > Option Explicit
> > > > > > > > > > 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
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > CLR wrote:
> > > > > > > > > > >
> > > > > > > > > > > Thanks Dave.........yeah, I finally did that but still
> no
> > > > > > > > > joy...........it
> > > > > > > > > > > don't give error messages any more, but it also don't
> give
> > > > > > > > > > > results........depending on what I type in the
=PULL(),
> I
> > > > > usually
> > > > > > > get
> > > > > > > > > > > #VALUE! or #REF!..........I've tried on both 97 and
> > > > > > > 2000.........I've
> > > > > > > > > even
> > > > > > > > > > > got both May 05 updates and still cant seem to get it
to
> > > > > > > work.......I
> > > > > > > > > guess
> > > > > > > > > > > maybe it's time to start back at square
> > > one..............some
> > > > > days
> > > > > > > the
> > > > > > > > > > > Dragon wins........
> > > > > > > > > > >
> > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > >
> > > > > > > > > > > "Dave Peterson" <petersod@verizonXSPAM.net> wrote in
> message
> > > > > > > > > > > news:42EAB61F.FE3FF849@verizonXSPAM.net...
> > > > > > > > > > > > You'll have to do the same kind of thing.
> > > > > > > > > > > >
> > > > > > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > > > > > becomes
> > > > > > > > > > > > n = InStrRev97(expr, "!")
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > CLR wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > Thanks Dave.........it got past that line by
> following
> > > your
> > > > > > > > > > > instructions, but
> > > > > > > > > > > > > now stops on
> > > > > > > > > > > > >
> > > > > > > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > > > > > >
> > > > > > > > > > > > > I tried modifying it, but no joy.....I'm just
> shooting
> > > in
> > > > > the
> > > > > > > dark.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > > >
> > > > > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > > > > >
> > > > > > > > > > > > > > Instrrev was added in xl2k.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > You could create your own InstrRev97 function
and
> use
> > > > > that:
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Function InStrRev97(mystr As Variant, mydelim As
> > > String)
> > > > > As
> > > > > > > Long
> > > > > > > > > > > > > > Dim i As Long
> > > > > > > > > > > > > > InStrRev97 = 0
> > > > > > > > > > > > > > For i = Len(mystr) To 1 Step -1
> > > > > > > > > > > > > > If Mid(mystr, i, 1) = mydelim Then
> > > > > > > > > > > > > > InStrRev97 = i
> > > > > > > > > > > > > > Exit Function
> > > > > > > > > > > > > > End If
> > > > > > > > > > > > > > Next i
> > > > > > > > > > > > > > End Function
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > (Just add this to the bottom of that general
> module.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > so
> > > > > > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > > > > > > becomes
> > > > > > > > > > > > > > n = InStrRev97(expr, "\")
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > =====
> > > > > > > > > > > > > > There are some other functions that were added
in
> xl2k
> > > > > (split
> > > > > > > for
> > > > > > > > > > > example). But
> > > > > > > > > > > > > > I took a cursory glance at Harlan's code and
> didn't
> > > see
> > > > > any
> > > > > > > others
> > > > > > > > > > > that would
> > > > > > > > > > > > > > cause you trouble.
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > (Post back when you see that I missed one!)
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > CLR wrote:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > I went after Harlan's UDF and got it but can't
> for
> > > the
> > > > > life
> > > > > > > of
> > > > > > > > > me
> > > > > > > > > > > figure out
> > > > > > > > > > > > > > > how to use it.
> > > > > > > > > > > > > > > I pasted it into a regular module and, all I
can
> get
> > > > > "Sub or
> > > > > > > > > > > function not
> > > > > > > > > > > > > > > defined" on this line..
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > n = InStrRev(Len(expr), expr, "\")
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Anybody see what I'm doing wrong?
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > "Dave Peterson" wrote:
> > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > You'd want to use the =indirect() worksheet
> > > function.
> > > > > But
> > > > > > > > > that
> > > > > > > > > > > doesn't work
> > > > > > > > > > > > > > > > with closed files.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Harlan Grove wrote a UDF called PULL that
will
> > > > > retrieve
> > > > > > > the
> > > > > > > > > value
> > > > > > > > > > > from a closed
> > > > > > > > > > > > > > > > workbook.
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > You can find the function at Harlan's FTP
> site:
> > > > > > > > > > > > > > > > ftp://members.aol.com/hrlngrv/
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > CLR wrote:
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Hi All........
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > I am trying to concatenate two cells
> together to
> > > > > form a
> > > > > > > > > filename
> > > > > > > > > > > in a
> > > > > > > > > > > > > > > > > link............no joy, .......all I get
is
> > > "That
> > > > > > > filename
> > > > > > > > > is
> > > > > > > > > > > not
> > > > > > > > > > > > > > > > > valid"........
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > =[clean(c6)&"_"&b6.xls]Sheet1!$A9
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > C6 is a name, as Jones, Fred
> > > > > > > > > > > > > > > > > B6 is a string as R1938
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > The filename I 'm looking for is
> > > > > Jones,Fred_R1938.xls
> > > > > > > and it
> > > > > > > > > > > does
> > > > > > > > > > > > > > > > > exist........and of course works if I hard
> code
> > > the
> > > > > > > filename
> > > > > > > > > > > into the
> > > > > > > > > > > > > > > > > formula........
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > =clean(c6)&"_"&b6 works fine in a test of
> that
> > > > > > > standalone
> > > > > > > > > part,
> > > > > > > > > > > but not in
> > > > > > > > > > > > > > > > > the link formula
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Any help would be appreciated........
> > > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > --
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > > > > Dave Peterson
> > > > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > --
> > > > > > > > > > > > > >
> > > > > > > > > > > > > > Dave Peterson
> > > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > > --
> > > > > > > > > > > >
> > > > > > > > > > > > Dave Peterson
> > > > > > > > > >
> > > > > > > > > > --
> > > > > > > > > >
> > > > > > > > > > Dave Peterson
> > > > > > > >
> > > > > > > > --
> > > > > > > >
> > > > > > > > Dave Peterson
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson
>
>