MSAccess - List folders in a directory VBA

Question

Dear All,

I need to to list all the Folders for a given folder in a VBA ListBox like this:

IT

UK

ZA

This is my folder and I need to show what I have highlighted:

I built the below code

Sub GetFolders(MyListBox As Object) 

    Dim objFSO As Object 

    Dim objFolders As Object 

    Dim objFolder As Object 

    Set objFSO = CreateObject("Scripting.FileSystemObject") 

    Set objFolders = objFSO.GetFolder("C:\Users\admin\Desktop\COUNTRY\") 

    For Each objFolder In objFolders.SubFolders 

        MyListBox.AddItem objFolder.Name 

    Next objFolder 

    Set objFSO = Nothing 

    Set objFolders = Nothing 

    Set objFolder = Nothing 

End Sub

but it seems to get a duplicated folder name like this:

Please, could you help me?

Thanks

Answer 1

Insert the following line above the For ... Next loop:

MyListBox.RowSource = ""

This clears the list box before adding the subfolder names.

Answer 2

I have added the above code you suggested but I have the same issue

    For Each objFolder In objFolders.SubFolders 

        MyListBox.RowSource = "" 

        MyListBox.AddItem objFolder.Name 

    Next objFolder

I have added the above code you suggested but I have the same issue.

I don't understand this code goes in loop for a long even if it has to read only 3 subfolders

Thanks

Answer 3

No, he said 'above' the line

    MyListBox.RowSource = "" 

    For Each objFolder In objFolders.SubFolders 

        MyListBox.AddItem objFolder.Name 

    Next objFolder

I'd do something more like

Me.MyListboxName.RowSource = ""
Call GetFolders(Me.MyListboxName)

Answer 4

No, he said 'above' the line

    MyListBox.RowSource = "" 



    For Each objFolder In objFolders.SubFolders 



        MyListBox.AddItem objFolder.Name 



    Next objFolder

I'd do something more like

Me.MyListboxName.RowSource = ""
Call GetFolders(Me.MyListboxName)

Same issue:

I really don't understand why this code goes in loop for a long time even if it has to read only 3 subfolders

Answer 5

Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then make the copy available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Post a link to the uploaded and shared file in a reply here.