CODEWITHSUNDEEP
vbams-access
enriq
enriq

Reputation: 83

Access VBA: Search multiple files in multiple directories

I have a table in MS-Access that contains the paths of directories. In the form, I have a listbox that contains the list of files to be searched. I use the following code:

    Set rst = dbs.OpenRecordset("SELECT * FROM mytable;")
    While Not rst.EOF        
         strPath = rst!Path
         For i = 0 To lstBox.ListCount - 1
            strFullPath = strPath & "\" & lstBox.ItemData(i)
            If Len(Dir(strFullPath)) <> 0 Then
            'Do something 
            End If  
         Next i
         rst.MoveNext
    Wend

This works perfectly fine but it is not the optimal solution. For example, in the table if I have 5 different paths of directories and in the listbox if I have 2 files to search, the loop runs always 10 times even if I find the 2 files in the first two iterations. As per the code, even if the file has already been found in the first directory path, it searches for the same file in the remaining 4 directories. Is there a better way to do it reducing the rumber of iterations?

In my case, I will normally have atleast 10 directory paths and 20 files in the listbox to search, so the loop will iterate for 200 times always which will bring down the performance.

Upvotes: 1

Views: 340

Answers (1)

citizenkong
citizenkong

Reputation: 679

Track how many of the files have been found, then break out of the loop when your target number is reached, i.e.

Dim FileCount as Long
FileCount = 0

Do While not rst.EOF

    'If file found:
        FileCount = FileCount + 1
        If FileCount = lstBox.ItemsSelected.Count Then Exit Do
    'Do stuff

Loop

You could even iterate over only the selected items in the ListBox to speed things up further - the ItemsSelected property contains this.

Upvotes: 1

Related Questions