+ Reply to Thread
Results 1 to 9 of 9

Return the directory/folder of the active file

  1. #1
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Return the directory/folder of the active file

    Hi Folks!

    Not much of a programmer and need some help.

    I want to put a button on my toolbar that when clicked will return the directory/folder name of the active file into cell A1.

    I know I can do this with a formula but I don't want to go that route. I would like some VBA code to do this.

    Any help is greatly appreciated!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Return the directory/folder of the active file

    Of course you need to save the file first.

    Please Login or Register  to view this content.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return the directory/folder of the active file

    Not sure what to do with that code.

    If I put it in a general module as such:

    Please Login or Register  to view this content.
    Then run it, nothing happens except that cell A1 is selected.

    Here's what I want to do...

    I have copies of the same file in many folders. Sometimes I open one copy of the file and make changes. I save the file then I have to copy the updated file to the other folders. Sometimes I forget what folder I opened the file from so I would like the folder name to appear in a cell when I click a button.

    For example, I open the file:

    C:\TV\DriftAngle\2Theta.xls

    I click a button on my toolbar and the folder name DriftAngle will be entered in cell A1.

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Return the directory/folder of the active file

    You may use:
    Please Login or Register  to view this content.
    for whole path, or:
    Please Login or Register  to view this content.
    for only folder name.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  5. #5
    Forum Contributor
    Join Date
    02-24-2013
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    317

    Re: Return the directory/folder of the active file

    Can't give you what you are asking for above, but how about this?
    It puts the directory path in cell A1, as well as in a message box.

    Please Login or Register  to view this content.

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return the directory/folder of the active file

    Quote Originally Posted by Izandol View Post
    Please Login or Register  to view this content.
    for only folder name.
    Thanks, works great.

    I was getting ready to use this clunky formula:

    =MID(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2),7,50)

    That's specific to my situation as the top level directory/path is always the same.

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return the directory/folder of the active file

    I went with Izandol's suggestion.

    Thanks for your time and effort!

  8. #8
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Return the directory/folder of the active file

    Press F1 with cursor in or near a command word to get specific help. Debug.Print puts output to the Immediate window. In the Visual Basic Editor (VBE) select the View menu to enable that window if needed.

    To do the next part that you wanted:
    Please Login or Register  to view this content.

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return the directory/folder of the active file

    Quote Originally Posted by Kenneth Hobson View Post

    To do the next part that you wanted:
    Please Login or Register  to view this content.
    OK, that worked also.

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 09-05-2013, 10:00 AM
  2. [SOLVED] Open file by macro from active folder path
    By xbr088 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-26-2013, 10:11 PM
  3. Replies: 2
    Last Post: 01-26-2012, 12:52 PM
  4. Return full file path for files in directory
    By jeffreybrown in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2011, 12:42 PM
  5. Find File in Directory(s) and Open Folder highlighting file
    By cpadude in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-08-2010, 05:39 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1