+ Reply to Thread
Results 1 to 17 of 17

VBA Code to get file names with path

  1. #1
    Registered User
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    53

    VBA Code to get file names with path

    Hello Friends,

    I need a code to get all excel file names in a folder. File names should be like full names as "C:\Users\Vikas'\Desktop\Mail Macro.xlsx" .

    Please let me know in case of any concern.

    Thanks,
    Liz.
    Last edited by Liz_Biz; 04-14-2014 at 09:24 AM.

  2. #2
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,532

    Re: VBA Code to get file names with path

    Perhaps like this?

    Please Login or Register  to view this content.
    Looking at your folder I'm not really sure of the full folder name. Does it stop at "Desktop" or is it Desktop\Mail?
    If so change sPath to "C:\Users\Vikas'\Desktop\Mail\" and make the same chamge in fName.


    Alf
    Last edited by Alf; 04-13-2014 at 09:01 AM.

  3. #3
    Registered User
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: VBA Code to get file names with path

    Hi Alf,

    Thank you so much for looking into this.

    Code is working as expected. This need a little add on.I have tried but failed.

    Is it possible that code will ask user to enter folder path ? Ex. Code will ask for "C:\Users\Vikas'\Desktop\"


    Thanks,
    Liz

  4. #4
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: VBA Code to get file names with path

    Here's another..

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,532

    Re: VBA Code to get file names with path

    Hi apo

    Nice code and this is the right answer the question Liz didn’t ask in her first post.

    I'm a bit puzzled by this part

    Please Login or Register  to view this content.
    I thought this would only find ".xls" files but it finds ".xlsx" and "xlsm" as well. Would you care to explain?

    Alf

  6. #6
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: VBA Code to get file names with path

    Hi Alf..

    I am not sure of the technical reason why.. but if you go to the Command prompt and type Dir *.xls when in a directory that has xls/xlsx/xlsm files.. it will list them all..

    I originally posted the code and workbook without the wildcard after the xls.. but then changed it (thinking i needed to) .. but like you say.. it is actually not needed..

  7. #7
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,532

    Re: VBA Code to get file names with path

    Thanks for feed back. This seems to be another case of "Excel moves in myserious ways its miracles to preform"

    Alf

  8. #8
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: VBA Code to get file names with path

    You got me thinking... so i did some gooogling...

    Here are some interesting discussions on it..
    http://www.mrexcel.com/forum/excel-q...lsx-files.html

    http://www.pcreview.co.uk/forums/dir...-t1628303.html

    btw.. thanks for the rep points..

  9. #9
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,532

    Re: VBA Code to get file names with path

    So it's back to basic sorry DOS of course.

    Well this is good to know since writing macros looking for excel files with specific formate is not that unusual.

    Alf

  10. #10
    Registered User
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: VBA Code to get file names with path

    Thanks Alf & Apo. I got my solution but can't mix it in my mind. Thank you so much.

  11. #11
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,532

    Re: VBA Code to get file names with path

    Hi Liz

    Thanks for feed back and rep.

    Alf

  12. #12
    Registered User
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: VBA Code to get file names with path

    Quote Originally Posted by apo
    Hi..

    I had a quick look at it..

    Are you saying you want to Select a File via a File Dialogue and then the code will show all files within the folder(meaning the folder that contains the selected file) with file paths shown..?

    But.. more importantly... I would like you to post these further questions in the existing thread you made (rather than via PM).. this helps others looking for the same kind of solution in time to come...
    Hi Apo,

    Here is my query.

    I want to select file(s) via dialog box and get path for that only and not for all files in that folder.

    It may be more than one.

    Thanks,
    Liz
    Last edited by Liz_Biz; 06-21-2014 at 01:04 AM.

  13. #13
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: VBA Code to get file names with path

    Try this...

    Change the InitialFilename path to suit..
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: VBA Code to get file names with path

    Apo,

    This is working as expected but its pasting path in cell (1,1). I need this in cell (2,6).

    Also, If I run it 2nd time, its pasting data from first cell again not from the last blank cell of 6th column.

    I know, I am giving you more trouble, but please help me. It can save my neck.

    Thanks,
    Liz.
    Last edited by Liz_Biz; 06-21-2014 at 08:55 AM.

  15. #15
    Valued Forum Contributor
    Join Date
    03-22-2013
    Location
    Australia,NSW, Wirrimbi
    MS-Off Ver
    Excel 2013
    Posts
    1,057

    Re: VBA Code to get file names with path

    You only need to change this:
    Please Login or Register  to view this content.
    to this:
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: VBA Code to get file names with path

    Excellent !! You helped me a lot. Thank you so much!!!

  17. #17
    Registered User
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: VBA Code to get file names with path

    Thanks!!! Excelforum.com!!!

+ 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. Code to list the folder path and sub folder path of a specific file
    By kalai1587 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2013, 03:51 AM
  2. Variable File and Path Names
    By GBR2L in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 06-02-2010, 10:53 AM
  3. code relative file path?
    By Glio in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2007, 03:02 AM
  4. [SOLVED] Indirect and Path & File Names
    By Anthony Slater in forum Excel General
    Replies: 3
    Last Post: 04-29-2005, 11:06 AM
  5. Path names to xla file in formula
    By Carl L in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-04-2005, 03:06 PM

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