Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
CLR
 
Posts: n/a
Default Concatinate a filename

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




  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #3   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in message
...
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



  #4   Report Post  
Govind
 
Posts: n/a
Default

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




  #5   Report Post  
CLR
 
Posts: n/a
Default

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

Vaya con Dios,
Chuck, CABGx3



"Govind" wrote in message
...
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








  #6   Report Post  
CLR
 
Posts: n/a
Default

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

  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #8   Report Post  
CLR
 
Posts: n/a
Default

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

  #9   Report Post  
CLR
 
Posts: n/a
Default

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





  #10   Report Post  
Dave Peterson
 
Posts: n/a
Default

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


  #11   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in message
...
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



  #12   Report Post  
Dave Peterson
 
Posts: n/a
Default

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" wrote in message
...
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
  #13   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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



  #14   Report Post  
Dave Peterson
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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
  #15   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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





  #16   Report Post  
Dave Peterson
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
  #17   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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



  #18   Report Post  
Dave Peterson
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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
  #19   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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



  #20   Report Post  
Dave Peterson
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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


  #21   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in

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



  #22   Report Post  
Ragdyer
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in

message
...
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" wrote in

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




  #23   Report Post  
CLR
 
Posts: n/a
Default

Yeah, I hear ya RD.........I like it that way too.

Now, I got a really weird thing to report.
Last weekend I bought a 366 Celeron laptop for $15 at a yard sale. It has
Win98SE on it with Office97 and only 32MB RAM. I just now copied the
PullMaster.xls and PullTest.xls I have been using here on my 1.2GHz AMD
Desktop with WinMe and Office97 and 2k and 512MB RAM (with which nothing
worked), over to the laptop. Once on the laptop and adjusting the InStrRev
lines per Dave's 97function, the durn thing worked perfectly!!!!!........go
figure.

So, this puts me in a quandry. I must go back to work tomorrow and try on
that machine again, (Win98SE, Office97, 512MB RAM) the same protocol I've
used here, thinking maybe I screwed something up Friday. If it works, fine,
the problem boils down to my machine here at home and I can live with that,
worry about it later. But if it dont work at work tomorrow, I have no idea
to say why it works on some machines and not on
others.........strange.......could it possibly be a "setting"
somewhere?...........or a rights restriction?


Vaya con Dios,
Chuck, CABGx3





"Ragdyer" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in

message
...
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" wrote in

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






  #24   Report Post  
Dave Peterson
 
Posts: n/a
Default

Well, this is a horse of a different color! (Or some statement of surprise!)

Just some guesses...

If you start with a brand new workbook on the troublesome pc--close all addins
and all other workbooks (just in case there's a name conflict (instrrev97() or
pull()).

And put the code in that new workbook.
Then create a new workbook with something in A1 of sheet1.
Back to the workbook with the code.
type = in A1 of sheet1
point at sheet1 a1 of that other workbook

swap to the other workbook and close/save it.

Then put the equivalent of this in B1:
=pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")

Ps. If you're gonna share the code with users of xl97, I'd just change the code
to always use instrrev97--but you could code around it if you really wanted.

pps. If you have addins loaded, you can close them by going into the VBE and
hitting ctrl-g.

Then typing:
workbooks("myaddin.xla").close



CLR wrote:

Yeah, I hear ya RD.........I like it that way too.

Now, I got a really weird thing to report.
Last weekend I bought a 366 Celeron laptop for $15 at a yard sale. It has
Win98SE on it with Office97 and only 32MB RAM. I just now copied the
PullMaster.xls and PullTest.xls I have been using here on my 1.2GHz AMD
Desktop with WinMe and Office97 and 2k and 512MB RAM (with which nothing
worked), over to the laptop. Once on the laptop and adjusting the InStrRev
lines per Dave's 97function, the durn thing worked perfectly!!!!!........go
figure.

So, this puts me in a quandry. I must go back to work tomorrow and try on
that machine again, (Win98SE, Office97, 512MB RAM) the same protocol I've
used here, thinking maybe I screwed something up Friday. If it works, fine,
the problem boils down to my machine here at home and I can live with that,
worry about it later. But if it dont work at work tomorrow, I have no idea
to say why it works on some machines and not on
others.........strange.......could it possibly be a "setting"
somewhere?...........or a rights restriction?

Vaya con Dios,
Chuck, CABGx3

<<snipped
  #25   Report Post  
CLR
 
Posts: n/a
Default

Hi Dave..........
Ok, I did everything you said, except the part about closing the Add-ins
didn't work too good, I got some kind of error message, so I just did Tools
Add-ins and un-checked them all.........then did all the other stuff and

still only get the #REF! in B1...........then I moved both the new files
over to the laptop, and they worked there fine again..............so, I
will check again tomorrow on the work computer and see how that
goes.........

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote in message
...
Well, this is a horse of a different color! (Or some statement of

surprise!)

Just some guesses...

If you start with a brand new workbook on the troublesome pc--close all

addins
and all other workbooks (just in case there's a name conflict

(instrrev97() or
pull()).

And put the code in that new workbook.
Then create a new workbook with something in A1 of sheet1.
Back to the workbook with the code.
type = in A1 of sheet1
point at sheet1 a1 of that other workbook

swap to the other workbook and close/save it.

Then put the equivalent of this in B1:
=pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")

Ps. If you're gonna share the code with users of xl97, I'd just change

the code
to always use instrrev97--but you could code around it if you really

wanted.

pps. If you have addins loaded, you can close them by going into the VBE

and
hitting ctrl-g.

Then typing:
workbooks("myaddin.xla").close



CLR wrote:

Yeah, I hear ya RD.........I like it that way too.

Now, I got a really weird thing to report.
Last weekend I bought a 366 Celeron laptop for $15 at a yard sale. It

has
Win98SE on it with Office97 and only 32MB RAM. I just now copied the
PullMaster.xls and PullTest.xls I have been using here on my 1.2GHz AMD
Desktop with WinMe and Office97 and 2k and 512MB RAM (with which nothing
worked), over to the laptop. Once on the laptop and adjusting the

InStrRev
lines per Dave's 97function, the durn thing worked

perfectly!!!!!........go
figure.

So, this puts me in a quandry. I must go back to work tomorrow and try

on
that machine again, (Win98SE, Office97, 512MB RAM) the same protocol

I've
used here, thinking maybe I screwed something up Friday. If it works,

fine,
the problem boils down to my machine here at home and I can live with

that,
worry about it later. But if it dont work at work tomorrow, I have no

idea
to say why it works on some machines and not on
others.........strange.......could it possibly be a "setting"
somewhere?...........or a rights restriction?

Vaya con Dios,
Chuck, CABGx3

<<snipped





  #26   Report Post  
CLR
 
Posts: n/a
Default

Ok, I'm back at work on this approx 800Mhz Win98SE (4.10.2222A) machine with
Office97 and 512MB RAM. I created two new books just like I did at home,
installed Harlan's code and Dave's 97Function and corrected the InStrRev
lines..... and all I get is #REF! with the external book closed, using this
formula

=pull("'C:\[PullTestWork.xls]Sheet1'!$A$1")

and it works ok with the external book open......the same response I got at
home on my desktop machine.

This formula in another cell returns the correct value, so that demonstrates
the file exists and no type-o's.

='C:\[PullTestWork.xls]Sheet1'!$A$1

So, the senario is:
At-work Win98Se machine, XL97................it don't work
At-home WinMe machine XL 97 & 2k...........it don't work
At-home WinMe machine XL97 no Add-ins...it don't work
El Cheapo laptop Win98SE XL97..................it works
Dave's Unknown OS XL2003........................it works

To re-iterate my original goal, I would like to have a worksheet with about
300 formulas that will take a CONCATENATED value and look it up in a range in
a closed workbook called from a CONCATENATED filename.....something like:
=VLOOKUP(C8&D8,C:\[A8&B8.XLS]Sheet1!$A$10:$B$100,2,FALSE)

To this point, the offered suggestions have been the INDIRECT formula which
only works with open external files, and the PULL UDF which is supposed to do
this, but has only seen limited success described above...........I would
love to use it, if it would be consistant between machines.

Any other ideas or suggestions, please?

Vaya con Dios,
Chuck, CABGx3





"CLR" wrote:

Hi Dave..........
Ok, I did everything you said, except the part about closing the Add-ins
didn't work too good, I got some kind of error message, so I just did Tools
Add-ins and un-checked them all.........then did all the other stuff and

still only get the #REF! in B1...........then I moved both the new files
over to the laptop, and they worked there fine again..............so, I
will check again tomorrow on the work computer and see how that
goes.........

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote in message
...
Well, this is a horse of a different color! (Or some statement of

surprise!)

Just some guesses...

If you start with a brand new workbook on the troublesome pc--close all

addins
and all other workbooks (just in case there's a name conflict

(instrrev97() or
pull()).

And put the code in that new workbook.
Then create a new workbook with something in A1 of sheet1.
Back to the workbook with the code.
type = in A1 of sheet1
point at sheet1 a1 of that other workbook

swap to the other workbook and close/save it.

Then put the equivalent of this in B1:
=pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")

Ps. If you're gonna share the code with users of xl97, I'd just change

the code
to always use instrrev97--but you could code around it if you really

wanted.

pps. If you have addins loaded, you can close them by going into the VBE

and
hitting ctrl-g.

Then typing:
workbooks("myaddin.xla").close



CLR wrote:

Yeah, I hear ya RD.........I like it that way too.

Now, I got a really weird thing to report.
Last weekend I bought a 366 Celeron laptop for $15 at a yard sale. It

has
Win98SE on it with Office97 and only 32MB RAM. I just now copied the
PullMaster.xls and PullTest.xls I have been using here on my 1.2GHz AMD
Desktop with WinMe and Office97 and 2k and 512MB RAM (with which nothing
worked), over to the laptop. Once on the laptop and adjusting the

InStrRev
lines per Dave's 97function, the durn thing worked

perfectly!!!!!........go
figure.

So, this puts me in a quandry. I must go back to work tomorrow and try

on
that machine again, (Win98SE, Office97, 512MB RAM) the same protocol

I've
used here, thinking maybe I screwed something up Friday. If it works,

fine,
the problem boils down to my machine here at home and I can live with

that,
worry about it later. But if it dont work at work tomorrow, I have no

idea
to say why it works on some machines and not on
others.........strange.......could it possibly be a "setting"
somewhere?...........or a rights restriction?

Vaya con Dios,
Chuck, CABGx3

<<snipped




  #27   Report Post  
Dave Peterson
 
Posts: n/a
Default

WinXP Home/xl2003 works.

=======
One more option (that I can think of).

You use a macro to build the formulas--no =indirect() allowed.

You'll just create formulas like:

='C:\[PullTestWork.xls]Sheet1'!$A$1

And populate your range of cells that way.

I'm not sure how your data is laid out or what you have to do, but maybe you
could put a button on the worksheet that says: Retrieve Values Now.

Then the macro assigned to that button builds those formulas.



CLR wrote:

Ok, I'm back at work on this approx 800Mhz Win98SE (4.10.2222A) machine with
Office97 and 512MB RAM. I created two new books just like I did at home,
installed Harlan's code and Dave's 97Function and corrected the InStrRev
lines..... and all I get is #REF! with the external book closed, using this
formula

=pull("'C:\[PullTestWork.xls]Sheet1'!$A$1")

and it works ok with the external book open......the same response I got at
home on my desktop machine.

This formula in another cell returns the correct value, so that demonstrates
the file exists and no type-o's.

='C:\[PullTestWork.xls]Sheet1'!$A$1

So, the senario is:
At-work Win98Se machine, XL97................it don't work
At-home WinMe machine XL 97 & 2k...........it don't work
At-home WinMe machine XL97 no Add-ins...it don't work
El Cheapo laptop Win98SE XL97..................it works
Dave's Unknown OS XL2003........................it works

To re-iterate my original goal, I would like to have a worksheet with about
300 formulas that will take a CONCATENATED value and look it up in a range in
a closed workbook called from a CONCATENATED filename.....something like:
=VLOOKUP(C8&D8,C:\[A8&B8.XLS]Sheet1!$A$10:$B$100,2,FALSE)

To this point, the offered suggestions have been the INDIRECT formula which
only works with open external files, and the PULL UDF which is supposed to do
this, but has only seen limited success described above...........I would
love to use it, if it would be consistant between machines.

Any other ideas or suggestions, please?

Vaya con Dios,
Chuck, CABGx3

"CLR" wrote:

Hi Dave..........
Ok, I did everything you said, except the part about closing the Add-ins
didn't work too good, I got some kind of error message, so I just did Tools
Add-ins and un-checked them all.........then did all the other stuff and

still only get the #REF! in B1...........then I moved both the new files
over to the laptop, and they worked there fine again..............so, I
will check again tomorrow on the work computer and see how that
goes.........

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote in message
...
Well, this is a horse of a different color! (Or some statement of

surprise!)

Just some guesses...

If you start with a brand new workbook on the troublesome pc--close all

addins
and all other workbooks (just in case there's a name conflict

(instrrev97() or
pull()).

And put the code in that new workbook.
Then create a new workbook with something in A1 of sheet1.
Back to the workbook with the code.
type = in A1 of sheet1
point at sheet1 a1 of that other workbook

swap to the other workbook and close/save it.

Then put the equivalent of this in B1:
=pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")

Ps. If you're gonna share the code with users of xl97, I'd just change

the code
to always use instrrev97--but you could code around it if you really

wanted.

pps. If you have addins loaded, you can close them by going into the VBE

and
hitting ctrl-g.

Then typing:
workbooks("myaddin.xla").close



CLR wrote:

Yeah, I hear ya RD.........I like it that way too.

Now, I got a really weird thing to report.
Last weekend I bought a 366 Celeron laptop for $15 at a yard sale. It

has
Win98SE on it with Office97 and only 32MB RAM. I just now copied the
PullMaster.xls and PullTest.xls I have been using here on my 1.2GHz AMD
Desktop with WinMe and Office97 and 2k and 512MB RAM (with which nothing
worked), over to the laptop. Once on the laptop and adjusting the

InStrRev
lines per Dave's 97function, the durn thing worked

perfectly!!!!!........go
figure.

So, this puts me in a quandry. I must go back to work tomorrow and try

on
that machine again, (Win98SE, Office97, 512MB RAM) the same protocol

I've
used here, thinking maybe I screwed something up Friday. If it works,

fine,
the problem boils down to my machine here at home and I can live with

that,
worry about it later. But if it dont work at work tomorrow, I have no

idea
to say why it works on some machines and not on
others.........strange.......could it possibly be a "setting"
somewhere?...........or a rights restriction?

Vaya con Dios,
Chuck, CABGx3

<<snipped





--

Dave Peterson
  #28   Report Post  
CLR
 
Posts: n/a
Default

Cool, Dave........I believe you've hit upon the answer. What I have is a
"Proficiency Matrix" that simply stated, lists names down the left column
and tasks across the top. Then, each employee has a file that lists those
items they've been trained to and the level. The formulas I need are to
concatenate each name with a task and look that up in the concatenated
name-employeenumber file to return their proficiency level at that point on
the matrix. In operation, the sysmgr selects a matrix, then edits the names
and tasks as desired, and so then it would seem ok to push the "Update"
button to make things happen. I don't know how to go about all that right
off the top of my head, but it all seems within the realm of possibility
for me being able to muddle through it.

Many many thanks Dave, you've been a lifesaver, and I do appreciate you
hanging in there with me.................

Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote in message
...
WinXP Home/xl2003 works.

=======
One more option (that I can think of).

You use a macro to build the formulas--no =indirect() allowed.

You'll just create formulas like:

='C:\[PullTestWork.xls]Sheet1'!$A$1

And populate your range of cells that way.

I'm not sure how your data is laid out or what you have to do, but maybe

you
could put a button on the worksheet that says: Retrieve Values Now.

Then the macro assigned to that button builds those formulas.



CLR wrote:

Ok, I'm back at work on this approx 800Mhz Win98SE (4.10.2222A) machine

with
Office97 and 512MB RAM. I created two new books just like I did at

home,
installed Harlan's code and Dave's 97Function and corrected the InStrRev
lines..... and all I get is #REF! with the external book closed, using

this
formula

=pull("'C:\[PullTestWork.xls]Sheet1'!$A$1")

and it works ok with the external book open......the same response I got

at
home on my desktop machine.

This formula in another cell returns the correct value, so that

demonstrates
the file exists and no type-o's.

='C:\[PullTestWork.xls]Sheet1'!$A$1

So, the senario is:
At-work Win98Se machine, XL97................it don't work
At-home WinMe machine XL 97 & 2k...........it don't work
At-home WinMe machine XL97 no Add-ins...it don't work
El Cheapo laptop Win98SE XL97..................it works
Dave's Unknown OS XL2003........................it works

To re-iterate my original goal, I would like to have a worksheet with

about
300 formulas that will take a CONCATENATED value and look it up in a

range in
a closed workbook called from a CONCATENATED filename.....something

like:
=VLOOKUP(C8&D8,C:\[A8&B8.XLS]Sheet1!$A$10:$B$100,2,FALSE)

To this point, the offered suggestions have been the INDIRECT formula

which
only works with open external files, and the PULL UDF which is supposed

to do
this, but has only seen limited success described above...........I

would
love to use it, if it would be consistant between machines.

Any other ideas or suggestions, please?

Vaya con Dios,
Chuck, CABGx3

"CLR" wrote:

Hi Dave..........
Ok, I did everything you said, except the part about closing the

Add-ins
didn't work too good, I got some kind of error message, so I just did

Tools
Add-ins and un-checked them all.........then did all the other

stuff and
still only get the #REF! in B1...........then I moved both the new

files
over to the laptop, and they worked there fine again..............so,

I
will check again tomorrow on the work computer and see how that
goes.........

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote in message
...
Well, this is a horse of a different color! (Or some statement of
surprise!)

Just some guesses...

If you start with a brand new workbook on the troublesome pc--close

all
addins
and all other workbooks (just in case there's a name conflict
(instrrev97() or
pull()).

And put the code in that new workbook.
Then create a new workbook with something in A1 of sheet1.
Back to the workbook with the code.
type = in A1 of sheet1
point at sheet1 a1 of that other workbook

swap to the other workbook and close/save it.

Then put the equivalent of this in B1:
=pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")

Ps. If you're gonna share the code with users of xl97, I'd just

change
the code
to always use instrrev97--but you could code around it if you really
wanted.

pps. If you have addins loaded, you can close them by going into the

VBE
and
hitting ctrl-g.

Then typing:
workbooks("myaddin.xla").close



CLR wrote:

Yeah, I hear ya RD.........I like it that way too.

Now, I got a really weird thing to report.
Last weekend I bought a 366 Celeron laptop for $15 at a yard sale.

It
has
Win98SE on it with Office97 and only 32MB RAM. I just now copied

the
PullMaster.xls and PullTest.xls I have been using here on my

1.2GHz AMD
Desktop with WinMe and Office97 and 2k and 512MB RAM (with which

nothing
worked), over to the laptop. Once on the laptop and adjusting the
InStrRev
lines per Dave's 97function, the durn thing worked
perfectly!!!!!........go
figure.

So, this puts me in a quandry. I must go back to work tomorrow

and try
on
that machine again, (Win98SE, Office97, 512MB RAM) the same

protocol
I've
used here, thinking maybe I screwed something up Friday. If it

works,
fine,
the problem boils down to my machine here at home and I can live

with
that,
worry about it later. But if it dont work at work tomorrow, I

have no
idea
to say why it works on some machines and not on
others.........strange.......could it possibly be a "setting"
somewhere?...........or a rights restriction?

Vaya con Dios,
Chuck, CABGx3

<<snipped




--

Dave Peterson



  #29   Report Post  
Dave Peterson
 
Posts: n/a
Default

Glad you're (kind of) happy.

If you get time, you may want to revisit the =pull() function to see if you can
get it to work.

CLR wrote:

Cool, Dave........I believe you've hit upon the answer. What I have is a
"Proficiency Matrix" that simply stated, lists names down the left column
and tasks across the top. Then, each employee has a file that lists those
items they've been trained to and the level. The formulas I need are to
concatenate each name with a task and look that up in the concatenated
name-employeenumber file to return their proficiency level at that point on
the matrix. In operation, the sysmgr selects a matrix, then edits the names
and tasks as desired, and so then it would seem ok to push the "Update"
button to make things happen. I don't know how to go about all that right
off the top of my head, but it all seems within the realm of possibility
for me being able to muddle through it.

Many many thanks Dave, you've been a lifesaver, and I do appreciate you
hanging in there with me.................

Vaya con Dios,
Chuck, CABGx3

"Dave Peterson" wrote in message
...
WinXP Home/xl2003 works.

=======
One more option (that I can think of).

You use a macro to build the formulas--no =indirect() allowed.

You'll just create formulas like:

='C:\[PullTestWork.xls]Sheet1'!$A$1

And populate your range of cells that way.

I'm not sure how your data is laid out or what you have to do, but maybe

you
could put a button on the worksheet that says: Retrieve Values Now.

Then the macro assigned to that button builds those formulas.



CLR wrote:

Ok, I'm back at work on this approx 800Mhz Win98SE (4.10.2222A) machine

with
Office97 and 512MB RAM. I created two new books just like I did at

home,
installed Harlan's code and Dave's 97Function and corrected the InStrRev
lines..... and all I get is #REF! with the external book closed, using

this
formula

=pull("'C:\[PullTestWork.xls]Sheet1'!$A$1")

and it works ok with the external book open......the same response I got

at
home on my desktop machine.

This formula in another cell returns the correct value, so that

demonstrates
the file exists and no type-o's.

='C:\[PullTestWork.xls]Sheet1'!$A$1

So, the senario is:
At-work Win98Se machine, XL97................it don't work
At-home WinMe machine XL 97 & 2k...........it don't work
At-home WinMe machine XL97 no Add-ins...it don't work
El Cheapo laptop Win98SE XL97..................it works
Dave's Unknown OS XL2003........................it works

To re-iterate my original goal, I would like to have a worksheet with

about
300 formulas that will take a CONCATENATED value and look it up in a

range in
a closed workbook called from a CONCATENATED filename.....something

like:
=VLOOKUP(C8&D8,C:\[A8&B8.XLS]Sheet1!$A$10:$B$100,2,FALSE)

To this point, the offered suggestions have been the INDIRECT formula

which
only works with open external files, and the PULL UDF which is supposed

to do
this, but has only seen limited success described above...........I

would
love to use it, if it would be consistant between machines.

Any other ideas or suggestions, please?

Vaya con Dios,
Chuck, CABGx3

"CLR" wrote:

Hi Dave..........
Ok, I did everything you said, except the part about closing the

Add-ins
didn't work too good, I got some kind of error message, so I just did

Tools
Add-ins and un-checked them all.........then did all the other

stuff and
still only get the #REF! in B1...........then I moved both the new

files
over to the laptop, and they worked there fine again..............so,

I
will check again tomorrow on the work computer and see how that
goes.........

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Dave Peterson" wrote in message
...
Well, this is a horse of a different color! (Or some statement of
surprise!)

Just some guesses...

If you start with a brand new workbook on the troublesome pc--close

all
addins
and all other workbooks (just in case there's a name conflict
(instrrev97() or
pull()).

And put the code in that new workbook.
Then create a new workbook with something in A1 of sheet1.
Back to the workbook with the code.
type = in A1 of sheet1
point at sheet1 a1 of that other workbook

swap to the other workbook and close/save it.

Then put the equivalent of this in B1:
=pull("'C:\My Documents\excel\[book2.xls]Sheet1'!$A$1")

Ps. If you're gonna share the code with users of xl97, I'd just

change
the code
to always use instrrev97--but you could code around it if you really
wanted.

pps. If you have addins loaded, you can close them by going into the

VBE
and
hitting ctrl-g.

Then typing:
workbooks("myaddin.xla").close



CLR wrote:

Yeah, I hear ya RD.........I like it that way too.

Now, I got a really weird thing to report.
Last weekend I bought a 366 Celeron laptop for $15 at a yard sale.

It
has
Win98SE on it with Office97 and only 32MB RAM. I just now copied

the
PullMaster.xls and PullTest.xls I have been using here on my

1.2GHz AMD
Desktop with WinMe and Office97 and 2k and 512MB RAM (with which

nothing
worked), over to the laptop. Once on the laptop and adjusting the
InStrRev
lines per Dave's 97function, the durn thing worked
perfectly!!!!!........go
figure.

So, this puts me in a quandry. I must go back to work tomorrow

and try
on
that machine again, (Win98SE, Office97, 512MB RAM) the same

protocol
I've
used here, thinking maybe I screwed something up Friday. If it

works,
fine,
the problem boils down to my machine here at home and I can live

with
that,
worry about it later. But if it dont work at work tomorrow, I

have no
idea
to say why it works on some machines and not on
others.........strange.......could it possibly be a "setting"
somewhere?...........or a rights restriction?

Vaya con Dios,
Chuck, CABGx3

<<snipped




--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
filename pointer tanhs Excel Worksheet Functions 0 June 21st 05 02:09 AM
Filename at the very top of Microsoft Excel Window Skyking Excel Discussion (Misc queries) 2 April 28th 05 04:25 AM
2 Question: Coloumn width, Filename nopfusch Excel Worksheet Functions 2 March 15th 05 08:43 PM
How do I join a filename and a cellreference, by just filling in . René Excel Worksheet Functions 1 February 23rd 05 03:24 PM
Insert value of a cell as a filename Ralph Howarth Excel Worksheet Functions 0 January 18th 05 01:03 AM


All times are GMT +1. The time now is 06:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"