Re: Concatinate a filename

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
.

Quantcast