Here’s some code that I stole from Chip Pearson that lists all the subdirectories given a starting directory. I’m installing a new server today and I’m trying to get my head around this crazy directory structure. This code provides a good first step, I think, but it wasn’t exactly speedy.
Sub ListSubs()
Dim fso As FileSystemObject
Dim StFldr As Folder
Dim Fldr As Folder
ActiveSheet.UsedRange.ClearContents
Set fso = New FileSystemObject
Set StFldr = fso.GetFolder(“S:Paragon”)
For Each Fldr In StFldr.SubFolders
SFolders Fldr
Next Fldr
Set StFldr = Nothing
Set fso = Nothing
End Sub
Sub SFolders(MFldr As Folder)
Dim Rng As Range
Dim SFldr As Folder
Set Rng = Range(“A65000”).End(xlUp).Offset(1, 0)
Rng.Value = MFldr.Path
If MFldr.SubFolders.Count > 0 Then
For Each SFldr In MFldr.SubFolders
SFolders SFldr
Next SFldr
End If
Set Rng = Nothing
End Sub
Dim fso As FileSystemObject
Dim StFldr As Folder
Dim Fldr As Folder
ActiveSheet.UsedRange.ClearContents
Set fso = New FileSystemObject
Set StFldr = fso.GetFolder(“S:Paragon”)
For Each Fldr In StFldr.SubFolders
SFolders Fldr
Next Fldr
Set StFldr = Nothing
Set fso = Nothing
End Sub
Sub SFolders(MFldr As Folder)
Dim Rng As Range
Dim SFldr As Folder
Set Rng = Range(“A65000”).End(xlUp).Offset(1, 0)
Rng.Value = MFldr.Path
If MFldr.SubFolders.Count > 0 Then
For Each SFldr In MFldr.SubFolders
SFolders SFldr
Next SFldr
End If
Set Rng = Nothing
End Sub
The huge drag on this routine is two-fold. First, and most importantly, writing each item to the spreadsheet one-by-one. Second, using an indirect method to write to the correct cell.
These problems can be solved by creating an array and filling it with the items. After the loop is finished, write the array to the sheet in one process: RANGE = array.
This solution makes it unnecessary to find the last empty cell. Without rewriting the loop routine, however, you can still get more efficiency by using COUNTA to find the number of entries in column A, and then Range(“A1?).OFFSET(COUNTA_result).value = fn to write the new entry to the sheet.
Stan Scott
New York City
Dick,
I stumbled on to your blog quite by accident.
What a pleasant surprise this has been!!
I am a regular Excel user and voraciously consume any information on Excel that I can find.
It’s almost a religion with me.
I am going to spend the next few weeks perusing the archives in your blog. I quickly ran through the topics listed. There is enough material there to satisfy a glutton.
I am by no means an Excel Guru.
You don’t have to be a high priest to be ranked among the most pious. The most ardent devotees are those who profess the religion sincerely, perform the rites daily, and not necessarily those with the most profound knowledge of its scriptures.
You could call my skill-level in Excel “Intermediate” but the geniuses at Microsoft who developed Excel will be happier with people like me who use their product more often, to do more work every single day of their lives.
I am now a self employed engineer running a steel design and detailing set up in India and I accept work outsourced from USA.
Prior to this I was a practising structural engineer by profession and worked for the Government for over 26 years and have been using computers since 1971. I began with IBM and DEC mainframes, and later used minicomputers and home computers before the PC revolution was kicked off in the eighties. I switched from Fortran to Basic in the eighties and later Quick Basic in the early nineties. I started with spreadsheets using Multiplan in the early eighties, moved over to 123 in the late eighties and finally graduated to Excel 5 in the mid nineties.
I had no further use for Quick basic or any programming language once VBA arrived on the scene.
I learned VBA basics using John Walkenbach’s book and was moderately successful in programming structural engineering applications till I finally outgrew all work that requires the use of one’s grey cells in my Govt Job. I needed Excel for the mundane paper work that senior managers used to delegate to their clerks and typists in the good old days but are now obliged to do by themseles in the privacy of their cabins. It is my regret that I never reached the levels of proficiency that is evident in the code being prolifically churned out by you and others in this blog.
I was a fanatic 123 user in the DOS era, and had to be dragged kicking and screaming to the Excel platform when DOS shared the Dodo’s fate and Windows let in fresh air in the world of software. Once I settled down, I was simply bowled over by Excel and I now use it like a Swiss Army knife for anything and everything.
I shed a tear drop now and then when I see an old 486 somewhere with Dos and Lotus still chugging along. Nostalgia is one of my incurable weaknesses.
Over the years I have propagated the use of Excel like a fanatic evangelist, taught it to my family, my colleagues, subordinates and have impressed my computer-illiterate bosses with some impressive output they never believed was possible.
Your blog has rekindled my interest in VBA programming and the producers of the local TV soap operas have just lost one set of eyeballs. This set will now be hooked on to Excel and VBA once again. I have quite a bit of catching up to do and I am sure I will be in pleasant company when I visit your blog at least once or twice a week or more often if time permits.
I just thought of writing to you to compliment you on your blog. This blog is DIFFERENT. It is useful, educative, and good fun. I look forward to posting comments now and then and hope you will pardon me when I display my ignorance of advanced VBA skills that all of you possess.
I hope to learn more of Excel and VBA in the illustrious company of the readers of this blog.
With best wishes
G Vishwanath
Bangalore, India
Hi – long time reader, never actually got round to posting.
I know it defeats the XL side of things but if you just wanted a directory listing and have access to the DOS prompt then the following will list all subdirectories into an excel file:
dir *. /s /b > a.xls
It works *very* quickly and you’ve got a directory listing one per line. If you wanted to embed this into a vba call this would also be pretty easy too.
Best regards,
Ashley
What reference must one make in order to get FOLDERS class to be recognized?
Using Exel 2003 Pro
Thanks
doco
doco: Microsoft Scripting Runtime.
Thanks Stan and Ashley. Those are good suggestions.
i am looking for VBA and excel training centre in bangalore
i am looking for VBA and excel training centre in bangalore.
Kindly give me the coaching centre number…even if someone could teach at my home would be even great
Provide me some contact number
@Ashley the simple solution you gave is exactly what I’d been looking for in many excel and vba forums. Thank you.