display folder and its sub folder after selection of drive in my Combo box RRS feed

  • Question

  • Hi experts,

    I have a form name Folder_File with one unbound combo name CboDrive two unbound listbox LstFolders and LstFiles.

    My CboDrive has this code (list of drives) C:\,D:\ so on..........

    Private Sub CboDrives_Change()
    GetFolders (CboDrives.Value)
    End Sub

    My LstFolders has this code

    Private Sub LstFolders_DblClick(Cancel As Integer)
    GetFiles (LstFolders.ItemData(LstFolders.ListIndex))

    My Folder_Form has this code

    Private Sub Form_Load()

      CboDrives.RowSource = ""
        GetFolders (CboDrives.Value)


    And I have this codes that I copy from the websites

    Option Compare Database

    Sub ShowFileList(folderspec)

    ' Show files of subfolder in the list box

        Dim fs, fl, f As Variant
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set fl = fs.GetFolder(folderspec)
        Set f = fl.Files
        For Each z In f
           LstFiles.AddItem z.Name
    End Sub
    Sub GetFiles(fol As String)

    ' Add files of subfolder to list box

        Dim fs, fl, f As Variant
         LstFiles.RowSourceType = "Value List"
        LstFiles.RowSource = ""
        Set fs = CreateObject("Scripting.FileSystemobject")
        Set fl = fs.GetFolder(fol)
        Set f = fl.Files
        For Each x In f
            LstFiles.AddItem x.Name
    End Sub
    Sub GetFolders(dr As String)

    ' Add subfolders of drive to list box

        Dim fs, fl, sf As Variant
         LstFolders.RowSourceType = "Value List"
         LstFolders.RowSource = ""
        Set fs = CreateObject("Scripting.Filesystemobject")
        Set fl = fs.GetFolder(dr & ":\")
        Set sf = fl.SubFolders
        For Each Y In sf
            LstFolders.AddItem dr & ":\" & Y.Name
    End Sub
    Sub GetDrives()

    ' Add drives of local machine to combo box

        Dim fs, dr As Variant
         CboDrives.RowSourceType = "Value List"
        Set fs = CreateObject("Scripting.FileSystemobject")
        Set dr = fs.Drives
        For Each x In dr
            CboDrives.AddItem x.DriveLetter
        CboDrives.Value = CboDrives.ItemData(0)
    End Sub


    It works so far but I have an issue as below;

    1. What I need to modify in the code that after I select from my CboDrives  I could also see the files and its sub folders?

           Ex. For drive B:\ a list of folders are populated in my LstFolders when i double click it could display both my sub folders in my LstSubf and the documents in my LstFiles in that folders. 

    LstFolders                         LstSubF                LstFiles                  

    Rev_12                              Manuals               Photo.jpeg


    after double click on sub folders

    LstFolders                         LstSubF                LstFiles                  
    Rev_12                              Completion          1.pdf               
                                                                          so on......

    2. I added one list box LstSubF in my Form_Filter where I wish the sub folders to display as illustrated above would it be possible? 

    Thanks in advance for the help,


    Friday, June 24, 2016 3:29 AM