Re: Concatinate a filename

Tech-Archive recommends: Speed Up your PC by fixing your registry

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" <RagDyer@xxxxxxxxxxxxx> wrote in message
news:uds3sSflFHA.4000@xxxxxxxxxxxxxxxxxxxxxxx
> Sorry Chuck, No help here from me ... JUST A Comment!
>
> Just tripping over this thread for the first time, and starting from the
> beginning to read it.
>
> It's a pleasure to be able to go from post to post with a mouse click, and
> start reading each one.
>
> LONG LIVE TOP POSTING! ! !<vbg>
> --
> Regards,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "CLR" <croberts@xxxxxxxxxxxxxxx> wrote in message
> news:eNwwWedlFHA.2860@xxxxxxxxxxxxxxxxxxxxxxx
> > No Error message after commenting out that line........
> >
> > It stopped on
> > Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro
> >
> > Cell still returns #VALUE!, but works fine if I open PullTest.xls
> >
> > I even tried going to Harlan's FTP site and downloading directly again,
> but
> > into xl2k this time and I still get the #VALUE! but this version doesn't
> > even work if I open the test file.........
> >
> > What to do, what to do?
> > I'm open to try any suggestions.............
> >
> > May I send you my PullMaster.xls and PullTest.xls directly and see if
they
> > will work on your 2003?.........or please send me your two that DO work
> > there and I can check them here on my installations of 2k and 97......
> >
> > Will xl2003 work on WinMe or does it need WinXP?
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in message
> > news:42ECC0CC.70186F76@xxxxxxxxxxxxxxxxxxx
> > > I think I used Harlan's code in xl97, xl2k and xl2002, and xl2003. So
I
> > don't
> > > think that's the trouble.
> > >
> > > Harlan traps errors and handles them in his code.
> > >
> > > Comment out this line:
> > >
> > > On Error GoTo CleanUp 'immediate clean-up at this point
> > >
> > > And see what kind of error message you get when you step through the
> code.
> > >
> > > (I still don't have a guess.)
> > >
> > > CLR wrote:
> > > >
> > > > Ok, thanks for the comeback...........so it works fine for you in
> 2003,
> > but
> > > > not in 2k or 97 on this machine nor on 97 at work.
> > > >
> > > > I've seen various versions of Harlan's code, which had both of the
> types
> > of
> > > > n = InStrRev(Len(xref), xref, "!") and
> > > > n = InStrRev(xref, "!") that you describe...........changed it in 2k
> and
> > it
> > > > didn't make any difference in the way it is acting.
> > > >
> > > > Did the breakpoint procedure on 2k and it stopped at the line......
> > > > Set xlwb = xlapp.Workbooks.Add 'needed by .ExecuteExcel4Macro
> > > > and gave me the #VALUE!...................does this tell you
> something?
> > > >
> > > > Did it again in 97 and it did not stop at all, went all the way
> through
> > but
> > > > still came up with the #REF
> > > >
> > > > So, does this mean that the PULL function will only work in
> > > > XL2003?.........is so, maybe that's the only answer?
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > > "Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in message
> > > > news:42EC29C3.64A4DF9D@xxxxxxxxxxxxxxxxxxx
> > > > > I did the same thing you did -- I created a c:\pulltest.xls in
> xl2003
> > and
> > > > > everything worked ok.
> > > > >
> > > > >
> > > > > I think there is still one typo in Harlan's code (but it didn't
> affect
> > my
> > > > test
> > > > > of your technique).
> > > > >
> > > > > This line:
> > > > >
> > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > should be:
> > > > > n = InStrRev(xref, "!")
> > > > >
> > > > > I guess the next thing to try is to reopen pullmaster.xls in xl97.
> > Change
> > > > those
> > > > > instrrev to instrrev97.
> > > > >
> > > > > Then put a break point on the first executable line in the
function.
> > > > >
> > > > > This line:
> > > > > n = InStrRev97(xref, "\")
> > > > >
> > > > > Then open pulltest.xls. Select one of the offending cells and hit
> F2,
> > > > then
> > > > > enter.
> > > > >
> > > > > The function should start, then stop on that break point line.
Then
> > you
> > > > can F8
> > > > > through the code to find what breaks.
> > > > >
> > > > > CLR wrote:
> > > > > >
> > > > > > Ok..........for some reason the FORMAT problem seemed to come
when
> I
> > > > copied
> > > > > > and pasted the invocation line from the Post to my
> > workbook........don't
> > > > > > understand that, it never happened before, but I'm over
> > it......that's
> > > > no
> > > > > > longer a problem.
> > > > > >
> > > > > > I just now opened a new workbook in XL97 . I then typed
"Success,
> > PULL
> > > > > > test" without quotes, in A1 and saved it directly to my C:\ as
> > > > PullTest.xls
> > > > > >
> > > > > > I then opened a new workbook (PullMaster.xls) and copied and
> pasted
> > > > Harlan's
> > > > > > code from your Post into Module1, and then copied and pasted
your
> 97
> > > > code
> > > > > > from your Post to the bottom of that same module, and changed
the
> > two
> > > > lines
> > > > > > in Harlan's code from InStrRev to InStrRev97 and I then hand
> typed
> > > > this in
> > > > > > A5
> > > > > > =pull("'C:\[PullTest.xls]Sheet1'!$A$1") My result was #REF!
> > > > > >
> > > > > > I then closed PullMaster.xls and re-opened it in XL2k. Cell A5
> > opens
> > > > as
> > > > > > #VALUE! so I then opened Module 1 and set the InStrRev lines in
> > > > Harlan's
> > > > > > code back to their original way, and cell A5 still reads
> > > > > > #VALUE!.............
> > > > > >
> > > > > > I was just about to post this message and decided to open
> > PullTest.xls
> > > > in
> > > > > > the background. Well, I did and A5 stayed as #VALUE! but when
I
> > > > deleted
> > > > > > the equal sign, and then re-inserted it again, the PULL function
> > worked
> > > > and
> > > > > > I got my value from PullTest.xls cell A1 that I was supposed to
> > > > > > get......only problem being is that the file I go after has to
be
> > > > > > OPEN..........
> > > > > >
> > > > > > I switched back to XL97 , re-set the InStrRev lines and the same
> > > > > > thing.......I started off getting the same #REF!, instead of the
> > #VALUE!
> > > > I
> > > > > > got in 2k, and when I opened PullTest.xls in the background, and
> > > > re=cycled
> > > > > > the Pull formulas, it all worked correctly, but only with the
> > external
> > > > file
> > > > > > OPEN.......which is what INDIRECT does.........
> > > > > >
> > > > > > Can you see if I have done anything wrong to make it not work
with
> > > > CLOSED
> > > > > > files?.........that is what I thought it was supposed to do, and
> > what I
> > > > need
> > > > > > it to do if possible.......
> > > > > >
> > > > > > Vaya con Dios,
> > > > > > Chuck, CABGx3
> > > > > >
> > > > > > "Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in message
> > > > > > news:42EBDA67.AC772A4@xxxxxxxxxxxxxxxxxxx
> > > > > > > The usual things that can cause the #name? errors:
> > > > > > >
> > > > > > > 1. You spelled the function one way in the work*** 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 work***, not behind ThisWorkbook).
> > > > > > > 3. Maybe you put the function in a different workbook's
project
> > by
> > > > > > mistake?
> > > > > > >
> > > > > > > I'd fix that #name? error before looking for more. But
> functions
> > > > don't
> > > > > > bring
> > > > > > > back formats. I'd suspect that the cell was formatted (as
> merged)
> > > > before
> > > > > > you
> > > > > > > started.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > CLR wrote:
> > > > > > > >
> > > > > > > > That will not be a problem........will only be retrieving
from
> > 1-10
> > > > > > > > characters or so, but thanks for the heads-up..........how
the
> > heck
> > > > do
> > > > > > you
> > > > > > > > guys find out about stuff like that anyway?
> > > > > > > >
> > > > > > > > As for the problem...........I started from scratch with
your
> > > > version of
> > > > > > > > Harlan's code and your 97 thing and all I could get was
> > > > > > #NAME?............I
> > > > > > > > went back and forth between 97 and 2k and switching the
> InStrRev
> > > > thing
> > > > > > back
> > > > > > > > and forth and all results were the same........#NAME?
> > > > > > > >
> > > > > > > > I even started with a new Book, and re-created the same path
> you
> > > > used in
> > > > > > > > your test and copied and pasted your test formula over to my
> > book
> > > > and
> > > > > > tried
> > > > > > > > 97 and 2k with and without the InStrRev thing and with the
> Book2
> > > > file
> > > > > > open
> > > > > > > > and closed and it still did the same thing.........#NAME?,
and
> > you
> > > > know
> > > > > > > > what?, it comes in as a merged cell 16 columns wide, whereas
> the
> > > > Book2
> > > > > > cell
> > > > > > > > A1 is only one column wide.
> > > > > > > >
> > > > > > > > Obviously it's something I'm doing wrong, but I can't for
the
> > life
> > > > of me
> > > > > > see
> > > > > > > > what it might be..............maybe if you were to tell me
the
> > EXACT
> > > > > > steps
> > > > > > > > you use to test it and I could follow them and see if I get
> the
> > same
> > > > > > > > results........
> > > > > > > >
> > > > > > > > Thanks for all your help,
> > > > > > > > Vaya con Dios,
> > > > > > > > Chuck, CABGx3
> > > > > > > >
> > > > > > > > "Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in message
> > > > > > > > news:42EAD25B.64DEA484@xxxxxxxxxxxxxxxxxxx
> > > > > > > > > One more word of warning. If you're returning any strings
> > longer
> > > > than
> > > > > > 255
> > > > > > > > > characters, this won't retrieve all the text (it's limited
> to
> > 255
> > > > > > > > characters).
> > > > > > > > >
> > > > > > > > > CLR wrote:
> > > > > > > > > >
> > > > > > > > > > Thanks Dave.............I'll have a go at it
> > tomorrow........I'm
> > > > > > totally
> > > > > > > > > > burnt out tonight..........fresh start in the morning
and
> it
> > > > will
> > > > > > > > probably
> > > > > > > > > > fall right in to place......just can't see the forrest
for
> > the
> > > > trees
> > > > > > > > > > tonight..........
> > > > > > > > > >
> > > > > > > > > > I do appreciate your time........this is actually quite
> > > > important to
> > > > > > me.
> > > > > > > > > > I'm starting a new project and this feature plays a
major
> > > > part.....I
> > > > > > > > just
> > > > > > > > > > need to calm down and make it work. I'll have maybe 300
> of
> > > > these
> > > > > > PULL's
> > > > > > > > on
> > > > > > > > > > each of about 50 Training Matrix Workbooks........they
> will
> > > > actually
> > > > > > be
> > > > > > > > > > inside concatenated VLOOKUPs, and will draw from
500-600
> > > > individual
> > > > > > > > > > employee files......and the whole thing tied together
with
> a
> > few
> > > > > > menu's
> > > > > > > > and
> > > > > > > > > > a little VBA........fun for me, and I learn something
new
> > > > > > > > > > everyday............
> > > > > > > > > >
> > > > > > > > > > Many, many thanks again,
> > > > > > > > > > Vaya con Dios,
> > > > > > > > > > Chuck, CABGx3
> > > > > > > > > >
> > > > > > > > > > "Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in
> message
> > > > > > > > > > news:42EAC719.67F023E3@xxxxxxxxxxxxxxxxxxx
> > > > > > > > > > > I was going to give you a google post:
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > >
> > > >
> >
>
http://groups.google.co.uk/group/microsoft.public.excel.work***.functions/
> > > > > > > > > > msg/e249f6c074a3adfd
> > > > > > > > > > > (one line in your browser)
> > > > > > > > > > >
> > > > > > > > > > > But google is adding extra characters in the code and
> > screwing
> > > > it
> > > > > > up.
> > > > > > > > > > >
> > > > > > > > > > > So I thought that a link to Harlan's FTP site would be
> > better.
> > > > > > But I
> > > > > > > > just
> > > > > > > > > > > looked at it and it's not up to date with what he's
> posted
> > on
> > > > the
> > > > > > > > > > newsgroups.
> > > > > > > > > > >
> > > > > > > > > > > So I used the (most???) current version that I saw on
> > google
> > > > and
> > > > > > tried
> > > > > > > > to
> > > > > > > > > > clean
> > > > > > > > > > > up those google induced errors.
> > > > > > > > > > >
> > > > > > > > > > > I tested it to make sure it works with a call like:
> > > > > > > > > > >
> > > > > > > > > > > =pull("'C:\My
Documents\excel\[book2.xls]Sheet1'!$A$1")
> > > > > > > > > > >
> > > > > > > > > > > Here's Harlan's code (but you'll need to still make
that
> > > > > > instrrev97
> > > > > > > > > > change):
> > > > > > > > > > >
> > > > > > > > > > > Option Explicit
> > > > > > > > > > > Function pull(xref As String) As Variant
> > > > > > > > > > > 'inspired by Bob Phillips and Laurent Longre
> > > > > > > > > > > 'but written by Harlan Grove
> > > > > > > > > > >
> > > > > >
'-----------------------------------------------------------------
> > > > > > > > > > > 'Copyright (c) 2003 Harlan Grove.
> > > > > > > > > > > '
> > > > > > > > > > > 'This code is free software; you can redistribute
it
> > > > and/or
> > > > > > modify
> > > > > > > > > > > 'it under the terms of the GNU General Public
> License
> > as
> > > > > > published
> > > > > > > > > > > 'by the Free Software Foundation; either version 2
> of
> > the
> > > > > > License,
> > > > > > > > > > > 'or (at your option) any later version.
> > > > > > > > > > >
> > > > > >
'-----------------------------------------------------------------
> > > > > > > > > > > '2004-05-30
> > > > > > > > > > > 'still more fixes, this time to address apparent
> > > > differences
> > > > > > > > between
> > > > > > > > > > > 'XL8/97 and later versions. Specifically, fixed
the
> > > > InStrRev
> > > > > > call,
> > > > > > > > > > > 'which is fubar in later versions and was using my
> own
> > > > hacked
> > > > > > > > version
> > > > > > > > > > > 'under XL8/97 which was using the wrong argument
> > syntax.
> > > > Also
> > > > > > > > either
> > > > > > > > > > > 'XL8/97 didn't choke on CStr(pull) called when
pull
> > > > referred
> > > > > > to an
> > > > > > > > > > > 'array while later versions do, or I never tested
> the
> > > > > > 2004-03-25
> > > > > > > > fix
> > > > > > > > > > > 'against multiple cell references.
> > > > > > > > > > >
> > > > > >
'-----------------------------------------------------------------
> > > > > > > > > > > '2004-05-28
> > > > > > > > > > > 'fixed the previous fix - replaced all instances
of
> > 'expr'
> > > > > > with
> > > > > > > > 'xref'
> > > > > > > > > > > 'also now checking for initial single quote in
xref,
> > and
> > > > if
> > > > > > found
> > > > > > > > > > > 'advancing past it to get the full pathname [dumb,
> > really
> > > > > > dumb!]
> > > > > > > > > > >
> > > > > >
'-----------------------------------------------------------------
> > > > > > > > > > > '2004-03-25
> > > > > > > > > > > 'revised to check if filename in xref exists - if
it
> > does,
> > > > > > > > proceed;
> > > > > > > > > > > 'otherwise, return a #REF! error immediately -
this
> > avoids
> > > > > > Excel
> > > > > > > > > > > 'displaying dialogs when the referenced file
doesn't
> > exist
> > > > > > > > > > >
> > > > > >
'-----------------------------------------------------------------
> > > > > > > > > > >
> > > > > > > > > > > Dim xlapp As Object, xlwb As Workbook
> > > > > > > > > > > Dim b As String, r As Range, C As Range, n As Long
> > > > > > > > > > >
> > > > > > > > > > > '** begin 2004-05-30 changes **
> > > > > > > > > > > '** begin 2004-05-28 changes **
> > > > > > > > > > > '** begin 2004-03-25 changes **
> > > > > > > > > > > n = InStrRev(xref, "\")
> > > > > > > > > > >
> > > > > > > > > > > If n > 0 Then
> > > > > > > > > > > If Mid(xref, n, 2) = "\[" Then
> > > > > > > > > > > b = Left(xref, n)
> > > > > > > > > > > n = InStr(n + 2, xref, "]") - n - 2
> > > > > > > > > > > If n > 0 Then b = b & Mid(xref, Len(b) + 2, n)
> > > > > > > > > > >
> > > > > > > > > > > Else
> > > > > > > > > > > n = InStrRev(Len(xref), xref, "!")
> > > > > > > > > > > If n > 0 Then b = Left(xref, n - 1)
> > > > > > > > > > >
> > > > > > > > > > > End If
> > > > > > > > > > >
> > > > > > > > > > > '** key 2004-05-28 addition **
> > > > > > > > > > > If Left(b, 1) = "'" Then b = Mid(b, 2)
> > > > > > > > > > >
> > > > > > > > > > > On Error Resume Next
> > > > > > > > > > > If n > 0 Then If Dir(b) = "" Then n = 0
> > > > > > > > > > > Err.Clear
> > > > > > > > > > > On Error GoTo 0
> > > > > > > > > > >
> > > > > > > > > > > End If
> > > > > > > > > > >
> > > > > > > > > > > If n <= 0 Then
> > > > > > > > > > > pull = CVErr(xlErrRef)
> > > > > > > > > > > Exit Function
> > > > > > > > > > > End If
> > > > > > > > > > > '** end 2004-03-25 changes **
> > > > > > > > > > > '** end 2004-05-28 changes **
> > > > > > > > > > >
> > > > > > > > > > > pull = Evaluate(xref)
> > > > > > > > > > >
> > > > > > > > > > > '** key 2004-05-30 addition **
> > > > > > > > > > > If IsArray(pull) Then Exit Function
> > > > > > > > > > > '** end 2004-05-30 changes **
> > > > > > > > > > >
> > > > > > > > > > > If CStr(pull) = CStr(CVErr(xlErrRef)) Then
> > > > > > > > > > > On Error GoTo CleanUp 'immediate clean-up at
> this
> > > > point
> > > > > > > > > > >
> > > > > > > > > > > Set xlapp = CreateObject("Excel.Application")
> > > > > > > > > > > Set xlwb = xlapp.Workbooks.Add 'needed by
> > > > > > .ExecuteExcel4Macro
> > > > > > > > > > >
> > > > > > > > > > > On Error Resume Next 'now clean-up can wait
> > > > > > > > > > >
> > > > > > > > > > > n = InStr(InStr(1, xref, "]") + 1, xref, "!")
> > > > > > > > > > > b = Mid(xref, 1, n)
> > > > > > > > > > >
> > > > > > > > > > > Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1))
> > > > > > > > > > >
> > > > > > > > > > > If r Is Nothing Then
> > > > > > > > > > > pull = xlapp.ExecuteExcel4Macro(xref)
> > > > > > > > > > >
> > > > > > > > > > > Else
> > > > > > > > > > > For Each C In r
> > > > > > > > > > > C.Value = xlapp.ExecuteExcel4Macro(b &
> > C.Address(1,
> > > > 1,
> > > > > > > > xlR1C1))
> > > > > > > > > > > Next C
> > > > > > > > > > >
> > > > > > > > > > > pull = r.Value
> > > > > > > > > > >
> > > > > > > > > > > End If
> > > > > > > > > > >
> > > > > > > > > > > CleanUp:
> > > > > > > > > > > If Not xlwb Is Nothing Then xlwb.Close 0
> > > > > > > > > > > If Not xlapp Is Nothing Then xlapp.Quit
> > > > > > > > > > > Set xlapp = Nothing
> > > > > > > > > > >
> > > > > > > > > > > End If
> > > > > > > > > > >
> > > > > > > > > > > End Function
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > > CLR wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > Thanks Dave.........yeah, I finally did that but
still
> > no
> > > > > > > > > > joy...........it
> > > > > > > > > > > > don't give error messages any more, but it also
don't
> > give
> > > > > > > > > > > > results........depending on what I type in the
> =PULL(),
> > I
> > > > > > usually
> > > > > > > > get
> > > > > > > > > > > > #VALUE! or #REF!..........I've tried on both 97 and
> > > > > > > > 2000.........I've
> > > > > > > > > > even
> > > > > > > > > > > > got both May 05 updates and still cant seem to get
it
> to
> > > > > > > > work.......I
> > > > > > > > > > guess
> > > > > > > > > > > > maybe it's time to start back at square
> > > > one..............some
> > > > > > days
> > > > > > > > the
> > > > > > > > > > > > Dragon wins........
> > > > > > > > > > > >
> > > > > > > > > > > > Vaya con Dios,
> > > > > > > > > > > > Chuck, CABGx3
> > > > > > > > > > > >
> > > > > > > > > > > > "Dave Peterson" <petersod@xxxxxxxxxxxxxxxx> wrote in
> > message
> > > > > > > > > > > > news:42EAB61F.FE3FF849@xxxxxxxxxxxxxxxxxxx
> > > > > > > > > > > > > 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()
work***
> > > > 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
> >
> >
>


.

Quantcast