Forum Home
Press F1
Thread ID: 106444	2010-01-08 12:54:00	Making Excel list of SubFolders within a Folder	KeyserSuzi (15556)	Press F1

Post ID	Timestamp	Content	User
847145	2010-01-08 12:54:00	Hi all - VBA noob here, would like some advice I managed to successfully follow the instructions in this thread (cheers andrew93): forums.pcworld.co.nz and amended it a bit to get a list of all sub folders within a given folder. Now I'm wondering if its possible to go down another level to get a list of any folders within these subfolders (subsubfolders?). The reason I'd like to do this is our users often lose folders by accidentally dragging one into another and I'd like to identify the lost ones. I realise I won't be able to tell which folders they are in with this method, but I suppose I could always find them with the windows search dog once I have the list. Anyway, here's the VBA code I'm currently using: Public Sub ListMyFiles() Dim fso As New FileSystemObject Dim fso_Folder As Folder.SubFolder Dim fso_File As Folder Dim file_count As Long Set fso_Folder = fso.GetFolder("s:\Northumbria Files\Electronic Case Record\North Shields") file_count = 0 For Each fso_File In fso_Folder.SubFolders file_count = file_count + 1 Cells(file_count, 1).Value = fso_File.Name Next fso_File Set fso = Nothing End Sub Any help with this would be much appreciated.	KeyserSuzi (15556)
847146	2010-01-09 01:18:00	I use directory lister to make lists of files. I am sure thsi program will do it, it is the best file lister I have used. I think it can make csv files as well as txt Directory Lister http://www.krksoft.com/ And its free !	Digby (677)
847147	2010-01-17 04:49:00	Digby, I just took a look at the krksoft site, following your URL, and it shows Directory Lister Pro as costing USD29.00. Have I missed something, or are you referring to the 30-day free trial?	tinakarori (5695)
847148	2010-01-18 00:22:00	Hi, unfortunately the file system object only lists subfolders directly under the target object folder so you would have to loop through each in turn infinitum. It's not the most efficient method so I just use plain old DOS. In Windows type CMD.exe under Run to get to the command prompt then type in something like this... DIR C:\Temp /A:D /O:N /S > C:\List.txt You can use DIR ? to see what the switch options are but the above means from the Directory C:\Temp list all directories (the A:D part) in alphabetical order (the O:N part) including subfolders (the /S part) and put the results in a file list.txt in the C drive (C:\List.txt). regards, Graham	Parry (5696)
847149	2010-01-18 00:24:00	Looks like this forum interprets colon then D as smileys so thats what the A then smiley mean above :D	Parry (5696)
847150	2010-01-18 00:27:00	Btw, if your using Win Vista or Win7 then it may not work initially. This is the security permissions kicking in so create a shortcut to CMD.exe then change the properties of the shortcut to "Run as Administrator" and it will work fine. regards, Graham	Parry (5696)
847151	2010-01-28 05:07:00	Hi Parry, This is completely off topic but I have been searching for you on the web for a while. We downloaded your 2006 fifa world cup wallchart and absolutely loved it - it got me into creating my own spreadsheets. just after a bit of information regarding certain elements of your world cup chart (penalty userforms), tried to send a private message but couldn't (sorry to those browsing this forum). could you send me a private message if possible, would really appreacite your help	lfc_reds (15557)
847152	2010-03-08 13:16:00	Thanks for all the advice - unfortunately I'm severely restricted as to what I'm able to do with the work computers - we're still on Windows 2000 and despite being IT tech support am severely limited in my powers. I ended up resorting to doing my checks manually by opening each folder and looking for nested ones - not fun when theres 27000 folders! I'm not allowed to download anything & can't even get a DOS prompt when logged into the work system, but think I may be able to from one specific computer in the office - I may have a go with Parry's solution as I'll need to be able to check these folders in future for housekeeping purposes. Thanks again.	KeyserSuzi (15556)
1