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