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

I agree with HansV, something else is going on with your code, but we'd need to see it, how you are calling the procedure...

Is GetFolders() being used in more than 1 event, if so you'd need to apply the same fix wherever else it is being used, or add the fix within the sub itself, or perhaps you have other similar procedures that also need adjusting in a similar manner.

Answer 2

I've tested your GetFolders sub-procedure, and, after initialising the list box's RowSource property to a zero-length string, it works exactly as expected, writing the set of subfolder names to the list box once only.

The only conclusion I can draw from this is that the GetFolders sub-procedure is being called three times in your case.   You should set a breakpoint at the head of whatever function or procedure calls the GetFolders sub-procedure, and step into the code line by line with the F8 key.  This should show you why the GetFolders sub-procedure is being called three times.  As the number of times the sub-procedure appears to be called equates with the number of subfolders, I would suspect that the calling code is iterating through the subfolders collection and calling the GetFolders sub-procedure at each iteration of the loop.

If you can't pin down the reason for the multiple procedure calls, if you initialise the list box's RowSource property to a zero length string within the sub-procedure like this:

    MyListBox.RowSource = ""

    For Each objFolder In objFolders.SubFolders

        MyListBox.AddItem objFolder.Name

    Next objFolder

The list box should be emptied before being filled again, however many times the GetFolders sub-procedure is called, so even though the error in the code will not have been been eliminated, only one set of subfolder names will be inserted into the list box.

Answer 3

Setting the RowSource to "" was my first suggestion. The OP replied that it doesn't help...

Answer 4

Setting the RowSource to "" was my first suggestion. The OP replied that it doesn't help...

I'm aware of that, but it doesn’t make sense.  If the RowSource property is initialised to a zero length string within the GetFolders sub-procedure then there is no way that the rows can be added to the control's RowSource three times.  I can only conclude that the OP has not done as you advised, which is why I reiterated what you had said, and showed exactly where the initialisation of the property should take place.

The only rational explanation of the behaviour which they are currently experiencing is that the procedure is being called three times, and the property is being initialised beforehand. The fact that the folder contains three subfolders, and the procedure is apparently being called three times suggests that the procedure is called at each iteration of a loop through the folder's subfolders collection in some code which they have not posted here.