List Files in Folder & Sub-Folders using Dir

  • I have posted this question here,

    http://stackoverflow.com/questions/33887223/list-all-files-in-a-folder-and-subfolders-in-excel?lq=1


    http://www.mrexcel.com/forum/e…sing-dir.html#post4353585


    This forum has also many related queries as in below links
    http://www.ozgrid.com/forum/showthread.php?t=174821


    http://www.ozgrid.com/forum/showthread.php?t=25774


    The above code is very near to what I want but it is slower when there are thousands of files in a folder. So I am trying to use dir or want to combine both dir & ScriptingObject and get the details as given by 1st code. I have got many answers and modified the code but not been able to get the details as in 1st code using Dir.





    The speed with FilesystemObject is slower compared to dir. I am trying to modify the 2nd code to give attributes "FileName (as Formula), Date Created, Date Last Accessed, Date Last Modified" as in the 1st code. Also If the list exceeds the row limit, another sheet with folder name-2 is created and files are listed.


    The path is in range of a sheet like Sheet1.Range("A2").End(Xlup) and not using filedialog or hardcoded, create folder tabs and run the code taking one folder path at a time. Sometimes when I run these codes i get Permission denied errors for recycle bin, perf logs etc for which i have no access, I have included "On Error Resume Next", but I want it to give which folder has errors and continue.
    1. Permission denied (available in 2nd code) as in "C:\PerfLogs"
    2. Formula errors if file names contains certain characters or if file has no extension. eg- "_INCAP~1" - Here i ext column it can give "No Extension" for such files.

    File listing of all files including subfolders


    Thanks in Advance

  • Re: List Files in Folder & Sub-Folders using Dir


    Here, written freehand and untested though:



    Change the directory and file extension in the first line as required.


    This should give you the core for what you need, you should be able to modify the "With" block to handle any errors etc. You can also have a google for the CMD DIR command and see how you can set other attributes for files to search for.

  • Re: List Files in Folder & Sub-Folders using Dir


    Thanks for the reply & suggestion. I will try and modify the code and post if I want any further clarification or help.

  • Re: List Files in Folder & Sub-Folders using Dir


    Hi Friends -


    I am again opening this post with certain additional requirements. Please help me resolve this, as I didn't get any convincing reply in other forums.

    http://www.mrexcel.com/forum/e…ubfolders-contents-2.html


    I have tried to adapt the code from the above link but not getting the desired result. The modified code is given below.


    I want the code to insert another sheet and continue listing the files when the number of files exceed the row limit of 1 million. Hope someone here can help solve this problem. Sometimes I get error when ifile = 1048577 or inserts new blank sheet without the files list. I tried to reset the counter, but it gives wrong file count i.e. number of files after the row limit and no files are listed.


    Thanks

  • Re: List Files in Folder & Sub-Folders using Dir


    Hi Friends


    Please anybody viewing this thread, please respond and suggest the way to get the desired result.


    Thanks

  • Re: List Files in Folder & Sub-Folders using Dir


    @S O - I have seen your answer and posted a reply to it. The above post is related to the 1st question but is a little different, so I added it here only as there will be continuity and unnecessary duplicate threads are not created for similar questions. Please see above, I have mentioned about reopening this conversation not asked as a new question.


    I have tried modifying the above code, but I am unable to do it. I think the offset property is giving me error as I cannot offset after the last row. Please suggest some changes so that I can get the desired result.


    Thanks in advance

  • Re: List Files in Folder & Sub-Folders using Dir


    @S O - Macro Man -


    Regarding your answer, it works well but is very similar in speed to the above first question code. So, I am using the above which reaches the end of row in about 7 - 10 minutes.


    Please! Please! Help me..Don't ignore my request as I am unable to get the result for the past 2 weeks whichever way I modify the code I am getting the error at line rOut.


    Thanks

  • Re: List Files in Folder & Sub-Folders using Dir


    Thanks shknbk2 for your reply. I reset the file counter, but the macro keeps running and inserts a new blank sheet, but the files are not listed. This macro lists the files in the immediate window and then gives the result after there are no files in folder to count. Please run this code on any of your folders and see what exactly needs to be done to solve my problem.


    Thanks

  • Re: List Files in Folder & Sub-Folders using Dir


    I changed a few things, but here's the entire code. Try it.


  • Re: List Files in Folder & Sub-Folders using Dir


    Thanks shknbk2 for your code and helping me to find a solution. The code is working fine. I will look into its details and get to know where exactly I went wrong in the first code. Once again, Thank you very much.


    Thanks

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!