Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    WorkBookName Custom Function (Excel 2000)

    Hello y'all,

    I've successfully created a custom (user defined) function that returns the active Workbook's name.

    Function WORKBOOKNAME() As String
    'Returns the workbook name of the cell
    'that contains the function
    WORKBOOKNAME = Application.Caller.Parent.Parent.Name
    End Function

    But now I have a client who wants the path as well as the Workbook Name.

    Any suggestions how to return the Workbook's oath and file name with a similar user define function?

    Thanks so much,
    Rich

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

    Re: WorkBookName Custom Function (Excel 2000)

    The formula

    =CELL("filename")

    or

    =CELL("filename",A1)

    will return the full path + file name of the active workbook.

    I don't know about workbook oaths... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  3. #3
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorkBookName Custom Function (Excel 2000)

    Hans,

    Yes, thanks for the =CELL tip.

    And, I was trying to find if there is a way that I could get the path (not oaths <img src=/S/blush.gif border=0 alt=blush width=15 height=15> ) in my user defined function? Any ideas?

    Thanks,
    Rich

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: WorkBookName Custom Function (Excel 2000)

    WORKBOOKNAME = Application.Caller.Parent.Parent.path

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorkBookName Custom Function (Excel 2000)

    Hi Steve,

    Wow, we're really close, most probably because I wasn't as clear as I should have been. Your suggestion certainly returned H:Excel ResourcesRich Created Add-Ins

    I was looking for something that included the Workbook name as well. It would return, for example: H:Excel ResourcesRich Created Add-Insmyworkbook.xls

    Thanks,
    rich

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

    Re: WorkBookName Custom Function (Excel 2000)

    Use FullName instead of Path. Or the following formula:

    =SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

  7. #7
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Vancouver, Washington, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorkBookName Custom Function (Excel 2000)

    Hans,

    Your FullName suggestion worked perfectly! It is just what we needed. You're a genius, and thanks so much!

    So, it looked like this:

    Function WORKBOOKPATH() As String
    'Returns the workbook name of the cell
    'that contains the function
    WORKBOOKPATH = Application.Caller.Parent.Parent.FullName
    End Function

    I distributed both of these, WORKBOOKNAME and WORKBOOKPATH, via an Excel Add-in.

    Tested successfully in MS Excel 2000 in WIN XP Pro, MS Excel 2004 in MAC OS 10.3.9, and MS Excel v.10 in MAC OS 10.3.9.

    Thanks so very much, all of you,
    Rich

Posting Permissions

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