How would I list out some sub-folders?

[greaseman]

I need to get a list of about 110 subfolders from within a folder. I searched high and low on this forum, but didn't see what I was hoping to find.

I tried putting a little Function together, but it gives an error. Here's the routine, and I commented the line that gives me an error:

Option Compare Database
Option Explicit

Public Function ListFolderss()

Dim strDBName As String

Dim rst1 As DAO.Recordset

Dim MyFile, MyPath, MyName

Set rst1 = CurrentDb.OpenRecordset("APCSProcess")

MyPath = "\\Jana-dp-data\projects\Honeywell-IHC\Sundstrand Legacy Data\APCS (Files for Conversion)"

MyName = Dir(MyPath, vbDirectory)

Do While MyName <> ""

' Ignore the current folder and the encompassing folder.
If MyName <> "." And MyName <> ".." Then

'**** This next line causes a 'File not found' error
If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
'****
rst1.AddNew
rst1!NameOfDataBase = MyName
rst1.Update
End If
End If
MyName = Dir
Loop

End Function

If any of you smart, intelligent folks would please either look at my code and advise me on the error of my ways, or suggest an alternative, I would be very grateful. Thank you in advance.... looking forward to your replies!

 

[nateobot]

Set a reference to Microsoft Scripting Runtime.

Sub printFolders()
Dim FS As New FileSystemObject
    Dim FSfolder As Folder
    Dim subfolder As Folder
    Dim i As Integer
    
    Set FSfolder = FS.GetFolder("C:\")
        
    i = 0
    For Each subfolder In FSfolder.SubFolders
        DoEvents
'do your rs stuff here
        Debug.Print subfolder.Name
        i = i + 1
    Next subfolder
    Set FSfolder = Nothing

End Sub

 

[greaseman]

Nateobot,

That worked! Thank you for your fast response!