Retrieving Folder SubFolder Names

[DMT Dave]

Hi guy's hope you are all well

I have had a look around on older systems i have and tried a few things but no success

I am trying to retrieve subfolder names within a folder, the following is an example, but access crashes on this code

Dim strPath As String, strFolders As String

strPath = "c:\"


strFolders = Dir(strPath, vbDirectory)


If Len(strPath & strFolders) > 0 Then


    While strFolders <> ""


    If (GetAttr(strPath & strFolders) And vbDirectory) = vbDirectory Then
    
        Debug.Print strFolders
    
    End If
    
    Wend
    
End If

I am not sure what If GetAttr does but found a few other people have added this into their folder listing



Once i get the folder names into Immediate window, i am going to then create a string and add to an input box so a user can select a number to open folder by using Application.FollwHyperlink

such as

Chr(149) & " " & " 1 " & Chr(149) & " To Open Folder Name 1 & vbcrlf & _
Chr(149) & " " & " 2 " & Chr(149) & " Tio Open Folder Name 2 etc...

What is the correct code for achieving this please ?

[moke123]

Sub slistfolders(strFolder, Optional IncludeSubFolders As Boolean = True)


    '    Dim fso As New FileSystemObject
    '    Dim fol As Folder,  sfol As Folder


    Dim fso As Object
    Dim fol As Object, sfol As Object
    Set fso = CreateObject("Scripting.filesystemobject")


    Set fol = fso.GetFolder(strFolder)


    For Each sfol In fol.SubFolders

                 debug.Print  sfol.Path
        
        If IncludeSubFolders = True Then
       
            slistfolders sfol.Path, IncludeSubFolders
        
        End If
        
    Next


End Sub

[DMT Dave]

Thanks Moke123, that worked a treat

[DMT Dave]

Hi Moke123, not only your suggestion works, i have added

Debug.Print mID(sfol.path, InStrRev(sfol.path, "\") + 1)

to retrieve just folder names and not full path

Excellent stuff, thanks again

[DMT Dave]

Hi Guy's, I thought I could clear this but after a slight adjustment to Moke123 suggestion

How can i add an array of numbers for each folder and add to an input box so a user can select a number to open the folder name ?

Immediate window displays folder names correctly

Dim fol As Object, sfol As Object, fso As Object 
    Set fso = CreateObject("Scripting.filesystemobject")
    
    strFolder = "T:\Dave\Images\Stored Images\"
    
    Set fol = fso.GetFolder(strFolder)


    For Each sfol In fol.SubFolders


        Debug.Print mID(sfol.path, InStrRev(sfol.path, "\") + 1)
        
    Next

How do i add something like

Dim iFolders as Integer, i as Integer

For i = 1 To mID(sfol.path, InStrRev(sfol.path, "\") + 1)

iFolders = InputBox("Enter Which Folder To Open ?" & vbcrlf & vbcrlf & _
Chr(149) & " " & i & " " Chr(149) & " " & Add Array Of Folder Names)

Hope i have asked this correctly to understand

Then i Can add something like

Select Case i

case = 1

Application.FollowHyperlink strFolder & FolderName

Etc.......

[Welshgasman]

Why would you want to use numbers?
I would give them a combo on a form, but show the full path, as you might have two names the same, but in different folders.

[DMT Dave]

Hello WGM, i have this now I think, was thinking of using listIndex so a user can select a number to open a folder, added to a listbox

Totally agree about combo but this is going to be integrated into a current procedure using a Option Control to save images to a selected folder but totally agree if this was a new procedure

Dim fs, sf, fl As Variant    Dim strInput As String
    
    Me.lstFolders.RowSource = "Value List"
    Me.lstFolders.RowSource = ""
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set fl = fs.GetFolder(strFolder)
    Set sf = fl.SubFolders
    
    For Each fl In sf
        Me.lstFolders.AddItem fl.Name
    Next
        Me.lstFolders.Requery
    
    For i = 0 To Me.lstFolders.ListCount - 1
        strList = strList & Me.lstFolders.ItemData(i) & vbCrLf
    Next
        strInput = InputBox("Enter Folder To Open ?" & vbCrLf & vbCrLf & _
            Chr(149) & " " & i & " " & Chr(149) & " " & strList, "ENTER NUMBER")

I have this very close but inputbox displaying both folder names but only 1 number

List box is set to no headers, is this to do with the 1st row being 0 (zero) ?

[DMT Dave]

So So Close with this, just can't open the correct folder, it's opening strfolder no matter which list index number is selected!!!

Dim fs, sf, fl As Variant    Dim iInput As Integer
    Dim strFolder As String
    
    Me.lstFolders.RowSource = "Value List"
    Me.lstFolders.RowSource = ""
    
    strFolder = "T:\DMT Ltd\Images\Stored Images\"
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set fl = fs.GetFolder(strFolder)
    Set sf = fl.SubFolders
    
    For Each fl In sf
        Me.lstFolders.AddItem fl.Name
    Next
        Me.lstFolders.Requery
    
    For i = 0 To Me.lstFolders.ListCount - 1


        strList = strList & Chr(149) & " " & i & " " & Chr(149) & " " & Me.lstFolders.ItemData(i) & vbCrLf & vbCrLf
        
    Next i
    
        iInput = InputBox("Enter Folder To Open ?" & vbCrLf & vbCrLf & _
            strList, "ENTER NUMBER")
        
        Application.FollowHyperlink strFolder & Me.lstFolders.ItemData(i) & "\"

[Welshgasman]

That is because you only append the number when you show the list, instead of appending for each folder.
Input boxes are hard to validate as well.

[DMT Dave]

ahh so if i am understanding you correctly, my For i line needs to be inside the next i ?

all is displaying correctly just always opening main folder and not subfolder

or if i am reading the sequence correct

instead of:
For i = 0 To Me.lstFolders.ListCount - 1
do this
for i = 0 to fl.Name

[moke123]

to retrieve just folder names and not full path

To just get the folder name use sfol.name

in the example double click the listbox to open folder.

[moke123]

In your code in post #8 your using late binding. Add a reference to MS Scripting runtime and the set the fs

Set fs = new FileSystemObject.

Then you'll have intellisense to help you out.

[moke123]

here's one way to do it using an inputbox

[DMT Dave]

Hi Moke123, will check to see what you have done thank you