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
|