Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Insert file name into a cell

    How do you have a cell insert the file name of the file (not the name of the worksheet).


    Excel 2000 (9.0 3821 SR-1)
    Thanks,

    Rick

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

    Re: Insert file name into a cell

    <pre>=CELL("filename")
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Insert file name into a cell

    I had a problem with this. It is inserting the entire path, in addition to the cell name: "c:My Document..." etc.
    Do I have a setting that is doing that, or is there a different formula I could use?

    Thanks again.

    Rick.

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

    Re: Insert file name into a cell

    Well, that is just a little more complicated:

    <pre>=MID(CELL("Filename"),FIND("[",CELL("Filename"))+1,FIND("]",CELL("Filename"))-FIND("[",CELL("Filename"))-1)
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Insert file name into a cell

    Thanks again...but one LAST question:
    The formula worked, but how do I make it NOT show the file extenstion, i.e. ".xls" and just the file name.

    Rick

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

    Re: Insert file name into a cell

    <pre>=MID(CELL("Filename"),FIND("[",CELL("Filename"))+1,FIND(".",CELL("Filename" ))-FIND("[",CELL("Filename"))-1)
    </pre>

    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Columbus, Ohio
    Posts
    234
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Insert file name into a cell

    Legare,
    You are remarkable. Thanks for making me look like the smart one in my office!!

    Rick

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Insert file name into a cell

    That's a nice little formula. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> I think it may be useful to have available as a custom function for future workbooks. Would it be possible to write it as a public function, such as =Filename(), and have it available for other workbooks? (Still for E2K, SR1)

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

    Re: Insert file name into a cell

    <pre>Public Function GetFilename(iType As Integer) As String
    Select Case iType
    Case 1
    GetFilename = Trim(Left(ActiveWorkbook.Name, _
    InStr(ActiveWorkbook.Name, ".") - 1))
    Case 2
    GetFilename = ActiveWorkbook.Name
    Case 3
    GetFilename = ActiveWorkbook.FullName
    End Select
    End Function
    </pre>


    To get the filename:
    <pre>=GetFilename(1)
    </pre>


    To get the filename + extension:

    <pre>=GetFilename(2)
    </pre>


    To get path + filename + extension:

    <pre>=GetFilename(3)
    </pre>

    Legare Coleman

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert file name into a cell

    Maybe the old Excel4 Get.Document(76) and Get.Document(88) might be an interesting alternative.
    You can use it (indirectly) as a worksheetfunction by going to Insert >> Name >> Define; type in an appropriate name (e.g. NameOfFile) and type the macro formula =GET.DOCUMENT(76) in the "Refers to:" edit area. In the Excel spreadsheet you can then use =NameOfFile as a worksheetfunction. No VBA required.

    =CELL("FileName") gives the path + workbook name + sheet name
    =GET.DOCUMENT(76) gives the workbook name + sheet name
    =GET.DOCUMENT(88) should give the workbook name alone
    Then you still have to get rid of the xls extension if needed.

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

    Re: Insert file name into a cell

    How about just path & filename? In addition, I am a novice at SQL. Can you walk me through the basic procedure for inserting this function into any excel file worksheet I open (both currently existing files and any newly created ones)?
    Thanks,
    Jeff

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

    Re: Insert file name into a cell

    By the way, I am running Excel 2000.
    Thanks,
    Jeff

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

    Re: Insert file name into a cell

    <pre>Public Function GetFilename(iType As Integer) As String
    Select Case iType
    Case 1
    GetFilename = Trim(Left(ActiveWorkbook.Name, _
    InStr(ActiveWorkbook.Name, ".") - 1))
    Case 2
    GetFilename = ActiveWorkbook.Name
    Case 3
    GetFilename = ActiveWorkbook.FullName
    Case 4
    GetFilename = Trim(Left(ActiveWorkbook.FullName, _
    InStr(ActiveWorkbook.FullName, ".") - 1))
    End Select
    End Function
    </pre>


    =GetFilename(4) will return the path + filename without the extension.

    To put this code into a workbook, you press Alt+F11 to get to the VBE environment. Then if there is no module in the workbook (look in the project explorer on the left of the screen), then select Module from the Insert menu to insert a menu. Paste the code into the module.

    If you want the code available to all workbooks, then you have two choices. You can put the code into your Personal.xls workbook (this workbook will be created if you record a macro and tell Excel to store the macro in your personal workbook), or you can put the function into an Excel addin.
    Legare Coleman

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

    Re: Insert file name into a cell

    I am really a novice here. Would someone PLEASE walk me through the procedure step-by-step? I currently have a Personal.XLS workbook that someone else created for me to add another function. I just really don't know how to do it!
    Thanks in advance,
    Jeff

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

    Re: Insert file name into a cell

    If you already have a Personal.xls, then do the following to add a macro to it:

    1- Start up Excel and press Alt+F11 to get into the VBE Environment.

    2- On the left side of the screen in the Project Explorer, you should see an object named VBAProject (PERSONAL.XLS). If this object has a "+" next to it, click on the "+" to expand the object.

    3- You should now see a collection named Modules. If the Modules collection has a "+" next to it, click on it to expand the collection.

    4- You should now see a list of module objects. There will most likely be only one module in the collection and it will most likely be named Module1 if someone has not changed the name from the default. You can either put your new macro into one of the modules in the list, or create a new module to put it in. Double click on the module you want to put the macro into. This should open the module in the VBA Editor in the right window. Scroll the right window until you are at the bottom of all of the code there and paste or type your new macro there.

    5- When you are done, close the VBE window, then close Excel and reply Yes when asked if you want to save the changes to Personal.xls.
    Legare Coleman

Posting Permissions

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