Create Combo with file Names from Folder

frimy
frimy used Ask the Experts™
on
Hello ALL,

How to collect all the FileName's from a folder and put the FileName's into a Combo box?

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
The following article should help : https://trumpexcel.com/vba-dir-function/
Senior Developer
Commented:
You can use DIR as built-in function or the FileSystemObject from the Microsoft Scripting library:

DIR:

Private Sub FillControl(ByVal CFolder As String, ByVal CControl As Access.Control)

  Dim File As String

  If TypeOf CControl Is Access.ComboBox Or TypeOf CControl Is Access.ListBox Then
    If FolderExists(CFolder) Then
      CControl.RowSourceType = "Value List"
      If Len(CFolder) > 0 And Right(CFolder, 1) <> "\" Then
        CFolder = CFolder & "\"
      End If

      File = Dir(CFolder & "*.*")
      Do
        If Len(Trim(File)) > 0 Then
          CControl.AddItem File
          File = Dir
        Else
          Exit Do
        End If
      Loop
    End If
  End If

End Sub

Open in new window


FileSystemObject (Early Binding):

Private Sub FillControl(ByVal CFolder As String, ByVal CControl As Access.Control)

  Dim File As Scripting.File
  Dim FileSystemObject As Scripting.FileSystemObject
  Dim Folder As Scripting.Folder

  If TypeOf CControl Is Access.ComboBox Or TypeOf CControl Is Access.ListBox Then
    Set FileSystemObject = New Scripting.FileSystemObject
    If FileSystemObject.FolderExists(CFolder) Then
      CControl.RowSourceType = "Value List"
      Set Folder = FileSystemObject.GetFolder(CFolder)
      For Each File In Folder.Files
        CControl.AddItem File.Name
      Next File

      Set File = Nothing
      Set Folder = Nothing
    End If

    Set FileSystemObject = Nothing
  End If

End Sub

Open in new window


FileSystemObject (Late Binding):

Private Sub FillControl(ByVal CFolder As String, ByVal CControl As Access.Control)

  Dim File As Object ' Scripting.File
  Dim FileSystemObject As Object ' Scripting.FileSystemObject
  Dim Folder As Object ' Scripting.Folder

  If TypeOf CControl Is Access.ComboBox Or TypeOf CControl Is Access.ListBox Then
    Set FileSystemObject = CreateObject("Scripting.FileSystemObject") ' New Scripting.FileSystemObject
    If FileSystemObject.FolderExists(CFolder) Then
      CControl.RowSourceType = "Value List"
      Set Folder = FileSystemObject.GetFolder(CFolder)
      For Each File In Folder.Files
        CControl.AddItem File.Name
      Next File

      Set File = Nothing
      Set Folder = Nothing
    End If

    Set FileSystemObject = Nothing
  End If

End Sub

Open in new window


All called as e.g.

Private Sub btnReadFolder_Click()

  If FolderExists(txtFolder.Value & "") Then
    FillControl txtFolder.Value, cboFilesByDir
    FillControl txtFolder.Value, lstFiles
  Else
    MsgBox "Folder not found.", vbExclamation + vbOKOnly
  End If

End Sub

Open in new window


You can place those methods in a standard module for better code reuse (chose on path).
EE29163318.accdb
Distinguished Expert 2017

Commented:
What is the purpose of storing the names in the combo?  You can open up the file dialog in Access and use that to pick a file.
Here's a picture of the form.  The button in the circle is cmdBrowse.  The click event code is below.  It places the name and path of a selected file in a control named txtDBName.
FileDialog.JPG

Private Sub cmdBrowse_Click()
    Me.txtDBName = fChooseFile()
End Sub

Open in new window


These two functions open the windows file dialog.  fChooseFile() returns a list of files after you navigate to a directory.  I also included fChooseDirectory because you might also find that one useful.  It returns a directory name but not a list of files.  Both functions require a reference to the Microsoft xx Object library.  "xx" will depend on what version of Office you are running.  Just pick the one you have installed.  It would be unlikely that you have more than one.  If you do, you should probably pick the oldest.
Public Function fChooseFile()
  
   ' Requires reference to Microsoft Office 11.0 Object Library.
 
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
 
 
   ' Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
   With fDialog
 
      ' Allow user to make multiple selections in dialog box
      .AllowMultiSelect = False
             
      ' Set the title of the dialog box.
      .Title = "Please select one file"
 
      'starting location
      .InitialFileName = CurrentProject.path
      
      ' Clear out the current filters, and add our own.
      .Filters.Clear
''''      .Filters.Add "Excel ", "*.XLSX"
      .Filters.Add "Access Databases", "*.ACCDB, *.MDB"
''''      .Filters.Add "Access Projects", "*.ADP"
      .Filters.Add "All Files", "*.*"
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
      If .Show = True Then
         'Loop through each file selected and add it to our list box.
         For Each varFile In .SelectedItems
            fChooseFile = varFile
''''            Me.FileList.AddItem varFile
         Next
        
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

End Function


Public Function fChooseDirectory()

    ' requires a reference to the Office xx Object library
    'Declare a variable as a FileDialog object.
    'Dim fd As FileDialog

   '''' Const msoFileDialogFolderPicker = 4 'use for late binding
    
    Dim fd As Object
    
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.

                'Only one item will be returned since the file dialog is a folder picker
                'MsgBox "The path is: " & vrtSelectedItem
                fChooseDirectory = vrtSelectedItem
                Exit Function
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing
    fChooseDirectory = "Error - nothing chosen"
End Function

Open in new window

Author

Commented:
thanks it works very good

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial