Check If a Listbox Already Has Files When Adding Files From a File Dialog {Folder]-CodePudding

Good day guys, I created a form in MS Access and I am trying to write some codes that prevents duplication of files when it is added to a listbox. As it is now, if I select the folder of interest and click on Display button, it re-adds the same files already in the listbox. Please advise. Below is my code so far:

    Public Sub btn_folderimp_Click()
    ' Displays Excel SpreadSheets In Selected Folder
    
        Dim myFSO As New FileSystemObject
        Dim MyFile As File
        Dim mFolder As Folder
    '   Dim lbx As ListBox
        
           
        'Checks If No Folder Was Selected
            If Nz(Me.txt_folderpath, "") = "" Then
                Beep
                MsgBox "No Folder Selected!"
                Exit Sub
            End If
        
        'Checks If Folder Exists, If Yes, it displays a list of spreadsheets in the Folder
            
            If myFSO.FolderExists(Nz(Me.txt_folderpath, "")) Then
                         
              
                Set mFolder = myFSO.GetFolder(Me.txt_folderpath)
    
                For Each MyFile In mFolder.Files
                
                            If (myFSO.GetExtensionName(MyFile.Name) = "xlsx") Then 'Or (myFSO.GetExtensionName(MyFile.Name) = "xls")
              
        
                                Me.lbx_show.AddItem MyFile.Path
    '                               Debug.Print MyFile
     
                            End If
                    
                Next MyFile
                
                           
    ' Checks if there are excel spreadsheets in the folder
    
              If Dir(Me.txt_folderpath & "*.xlsx") = "" Then
                    Beep
                    MsgBox "No Excel Spreadsheet Found!"
                    End If
              Else
                Beep
                MsgBox "Folder Does Not Exist"
              End If
       
    End Sub

CodePudding user response：

I guess you want to add for each directory you choose in the textbox the files to the listbox and if you run the code in your post a second time it should not add the same file a second time. You can do that by using a dictionary and adding the filenames to the dictionary

Option Compare Database
Option Explicit

' Dictionary for the filenames
Dim dict As New Scripting.Dictionary


Public Sub btn_folderimp_Click()
    ' Displays Excel SpreadSheets In Selected Folder
    
    Dim myFSO As New FileSystemObject
    Dim MyFile As File
    Dim mFolder As Folder
    '   Dim lbx As ListBox
        
           
    'Checks If No Folder Was Selected
    If Nz(Me.txt_folderpath, "") = "" Then
        Beep
        MsgBox "No Folder Selected!"
        Exit Sub
    End If
            
    ' clear the listbox
    lbx_show.RowSource = ""
        
    'Checks If Folder Exists, If Yes, it displays a list of spreadsheets in the Folder
            
    If myFSO.FolderExists(Nz(Me.txt_folderpath, "")) Then
                         
              
        Set mFolder = myFSO.GetFolder(Me.txt_folderpath)
    
        For Each MyFile In mFolder.Files
                
            If (myFSO.GetExtensionName(MyFile.Name) = "xlsx") Then 'Or (myFSO.GetExtensionName(MyFile.Name) = "xls")
              
                ' will add a new entry if it not exists
                dict(MyFile.Path) = MyFile.Path
                'Me.lbx_show.AddItem MyFile.Path
                
     
            End If
                    
        Next MyFile
        
        ' add the filenames to the listbox
        Dim key As Variant
        For Each key In dict.Keys
            Me.lbx_show.AddItem key
        Next
        
                           
        ' Checks if there are excel spreadsheets in the folder
    
        If Dir(Me.txt_folderpath & "*.xlsx") = "" Then
            Beep
            MsgBox "No Excel Spreadsheet Found!"
        End If
    Else
        Beep
        MsgBox "Folder Does Not Exist"
    End If
       
End Sub