Re: Concatinate a filename

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.worksheet.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() 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
.

Relevant Pages

  • Re: DTS Error - Data too large for specified buffer size
    ... to 0 and moved my row with the long string to the top of the file. ... > Did you look at Google? ... >> The destination column size is 500 in the table and the data in the ... >> import file for that row and column is 280 characters. ...
    (microsoft.public.sqlserver.dts)
  • Re: sha1 algorithm .dll or source ?
    ... > I have a text string of 100-200 characters as a variable in a program I'm ... writing. ... Google for it. ...
    (comp.lang.pascal.delphi.misc)
  • Re: UDF runs in 2K, dont in 97
    ... CLR wrote... ... > KillText = CharVal ... Function KillTextAs String ... I'd generalize it to take the set of characters to retain as an ...
    (microsoft.public.excel.programming)
  • Re: How to convert Infix notation to postfix notation
    ... If this is for an error message, why isn't it using stderr for its output? ... array of 15 characters, and you call this function with the limit 15 on ... Making sure that the only string I allocate and append to, ... because mulFactor in all versions must needs incorporate the functions ...
    (comp.lang.c)
  • Re: Prothon should not borrow Python strings!
    ... """It does not make sense to have a string without knowing what encoding ... same cul de sac as Python. ... Prothon_String_As_ASCII // raises error if there are high characters ... Python's split between byte strings and Unicode strings is ...
    (comp.lang.python)