Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Filename() (2003)

    Is there a function display the last workbook name in a link? For example, in cell A1 I have a link to an external workbook with its full name: C:/Folder A/Folder B/Folder C/Master Workbook.XLS. Is there a function, that would be located in another cell, that will strip away all but the "Master Workbook" and display it?
    Thanks in advance.

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Filename() (2003)

    Thanks, Tony, but that displays only the "current" workbook/file name. What I am looking to do is display the filename of the link!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filename() (2003)

    Copy this function into a module in the Visual Basic Editor

    Function ExtractFilename(oRange As Range) As String
    Dim strFormula As String
    Dim intPos1 As String
    Dim intPos2 As String
    strFormula = oRange.Formula
    intPos1 = InStr(strFormula, "<!t>[")
    intPos2 = InStr(intPos1 + 1, strFormula, "]<!/t>")
    ExtractFilename = Mid(strFormula, intPos1 + 1, intPos2 - intPos1 - 1)
    End Function

    Use it like this in a formula:

    =ExtractFilename(A1)

    If you store the function in your personal macro workbook Personal.xls, use

    =Personal.xls!ExtractFilename(A1)

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filename() (2003)

    How about:

    <code>=MID(A1,FIND("*",SUBSTITUTE(A1,"/","*",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,LEN(A1))</code>

    Based on your example that uses "/", though it should be ""

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Filename() (2003)

    Thanks, Hans and Tony. One follow-up for Hans: is there a way to strip the file extension of .xls as well?
    Thanks again.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filename() (2003)

    Assuming that the file name doesn't contain a point other than the one before the extension, you can change <code>"]"</code> to <code>"."</code>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filename() (2003)

    My and Tony's solution are based on completely different premises. Which one works for you?

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Filename() (2003)

    Hans,
    Yours does-I can't get Tony's to work.
    Thanks,
    Jeff

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filename() (2003)

    Tony's formula assumes that the filename is in the cell's value, my function assumes that it is in the cell's formula (since you mentioned that you had a link to an external workbook).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •