Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Torn between Kent & Essex, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieving Workbook Filename

    Does anyone know a way to retrieve the filename (but preferably not the full path) of a workbook? I've had a look at =INFO(directory) but to no avail. Am I stuck with doing it through VBA? If so, HOW?
    I'm using excel 9.0.2720

    TIA
    Gavin

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving Workbook Filename

    I don't know of any way to do this with Worksheet functions. In VBA, the following will give you the workbooks file name without the path:

    <pre> sName = ActiveWorkbook.Name
    </pre>


    The following will give you the path:

    <pre> sPath = ActiveWorkbook.Path
    </pre>


    The following VBA function could be used in a worksheet to put the Workbook name into a cell:

    <pre>Public Function GetWBName () As String
    GetWBName = ActiveWorkbook.Name
    End Function
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving Workbook Filename

    =cell("filename")
    will retrieve the file name and path

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Retrieving Workbook Filename

    Hi Gavin,
    You can use =Cell("filename",A1) to return the full filename and path (including tab name)
    If you just want the workbook name then I think something like this would work:
    =MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)
    There may be a shorter formula!
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Lounger
    Join Date
    Feb 2001
    Location
    Torn between Kent & Essex, England
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving Workbook Filename

    Thank you all.
    I went with Legare's suggestion as it saves me having to get rid of the path and extension, but they were all valid.

Posting Permissions

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