VBA example - Microsoft Access: show subfolders in a folder


VBA show subfolders in a folder

In this example, you will learn VBA display subfolders in a folder. All subfolders are populated in the the list box. When you select any drive in a combo box, all subfolders that contain in that drive will display in the list box. The example illustrates a Folder form that has a combo box(CboDrives), a list box(LstFolers). See the figure below:

 VBA example - Microsoft Access VBA Show Folders 

-When the form loads, the drive C is selected and its subfolders populated in the list box.(See Folder form above).

-If you want to select any drive, click arrow down of combo box and select the drive. For instance, you select drive D, all subfolders of this drive will populate in the list box.

To have a form as the figure above, you have to create the form in Form Design. See the figure below:

VBA example - Microsoft Access VBA Show Folders Form Design 

Note: Unselect Use Control Wizards before you drag these controls to the form.

- Drag and drop a combo box, and a list box to the form.

- Set Name properties of the two controls:

Name: CboDrives

Name: LstFolers

 

Then apply the VBA Code below:

  

Private Sub Form_Load()

' Add drives to combo box

    CboDrives.RowSourceType = "Value List"

    CboDrives.RowSource = ""

    CboDrives.AddItem "C:\"

    CboDrives.AddItem "D:\"

' Display drive C in combo box

    CboDrives.Value = "C:\"

    GetFolders (CboDrives.Value)

End Sub

..............................................................................

Private Sub CboDrives_Change()

    GetFolders (CboDrives.Text)

End Sub

..............................................................................

Sub GetFolders(ByVal dr As String)

' Diplay subfolder in the folder into list box

    Dim fs, sf, fl As Variant

    LstFolders.RowSourceType = "Value List"

    LstFolders.RowSource = ""

    Set fs = CreateObject("Scripting.FileSystemObject")

    Set fl = fs.GetFolder(dr)

    Set sf = fl.SubFolders

    For Each f In sf

        LstFolders.AddItem f.Name

    Next

End Sub



Comments

Michael comment

 Michael

There is no dim statement for the f variable in your code. Your code does not work without the statement!


2016-04-23



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.