Search/List subfolders using Dir

[mc84excel]

I prefer (where possible) to use Dir over FSO to take advantage of better running speed.

However FSO has the edge on Dir in that Dir is unable to natively process any files/folders below the original starting folder path. (A better way of putting it = Dir cant search the contents of any subfolders in the folder it is processing)

I have adapted code to return folder names using Dir (see http://www.excelforum.com/excel-prog...using-fso.html ).

I am not that good at writing recursive functions (especially when the output has to go to the same dynamic array each loop!) So could some kind forum user please adapt my adapted code so that it can process all subfolders and output the result to a single array?

[Leith Ross]

Hello mc84excel ,

Try this macro I wrote using the Dir function.

Recursively List Files and Folders Using Dir

Please Login or Register  to view this content.

Example of Calling the Macro
This will list all files and subfolders on the ActiveSheet in columns "A:B". Folder names are in bold in "A" and file names in "B".

Please Login or Register  to view this content.

[mc84excel]

Thanks for the suggestion Leith. Your code is interesting (I never learnt collections). I tried your code but it crashes Excel somewhere after 12,000 lines.

Also whenever the code encounters a folder that ends with a space (which Windows treats these as invalid see https://msdn.microsoft.com/en-au/lib...(v=vs.85).aspx ), VBA throws an Error 53. I added an error handler but it still crashes.

[Leith Ross]

Hello mc84excel,

I updated the code to handle errors for both files and folders. I tested this on my machine using "C:\Users". It filled 36,020 rows without crashing.

Please Login or Register  to view this content.

[mc84excel]

Thank you. Unfortunately the new version still CTD. Run time error 29 Out of stack space.

[Leith Ross]

Hello mc84excel,

I can't troubleshoot your system. You're on your own.

[mc84excel]

I can't troubleshoot your system. You're on your own.

nvm. I managed to adapt the function I was using. I have run it on the same folder I was testing all along and it worked fine. So it can't be the system causing the problem, I think it must have been due to using a Collection!


Code below for anyone who needs it. Very rough, I haven't tidied it up.

Please Login or Register  to view this content.