Link to home
Start Free TrialLog in
Avatar of maximyshka
maximyshka

asked on

List file names from a directory

Hi,
I need help with vba code to List file names from a directory in a combo box.
I use ms access 2007

thanks for any help
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<No Points wanted, as capricorn1 has already answered your direct question here>

You can also call up the Windows dialog box to select a file (if that is your ultimate goal)
One benefit to the dialog box is that you can browse to any drive folder.
And also view files in Thumbnail, List, Details, ...etc)

http://access.mvps.org/access/api/api0001.htm

;-)

JeffCoachman
Here is yet another way to do this (with some filtering):
Public Sub FillComboBoxList(cbo As Access.ComboBox, strSourcePath As String)
'Created by Helen Feddema 10-May-2012
'Last modified by Helen Feddema 20-May-2012

On Error GoTo ErrorHandler

   Dim fso As New Scripting.FileSystemObject
   Dim fld As Scripting.Folder
   Dim fil As Scripting.File
   
   Set fld = fso.GetFolder(strSourcePath)
   
   For Each fil In fld.Files
      Debug.Print fil.Name
      If Right(fil.Name, 3) = "xls" Or Right(fil.Name, 4) = "xlsx" Then
         If InStr(fil.Name, "Copy") = 0 And InStr(fil.Name, "~") = 0 Then
            cbo.AddItem fil.Name
         End If
      End If
   Next fil
      
ErrorHandlerExit:
   Set fso = Nothing
   Set fld = Nothing
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in FillComboBoxList procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

And another -- this one stores the file names in a table:

Public Sub CopyCSVFilesToTable()
'Created by Helen Feddema 6-Sep-2010
'Last modified by Helen Feddema 6-Sep-2010

On Error GoTo ErrorHandler

   Dim fso As New Scripting.FileSystemObject
   Dim fld As Scripting.Folder
   Dim fil As Scripting.File
   Dim strSourcePath As String
   Dim strTable As String
   Dim strSQL As String
   Dim rst As DAO.Recordset
   
   strSourcePath = "G:\Documents\Job Files To Import"
   strTable = "tblFiles"
   strSQL = "DELETE * FROM " & strTable
   DoCmd.RunSQL strSQL
   
   Set fld = fso.GetFolder(strSourcePath)
   Set rst = CurrentDb.OpenRecordset(strTable)
   For Each fil In fld.Files
      Debug.Print fil.Name
      If Right(fil.Name, 3) = "csv" Then
         rst.AddNew
         rst![FileName] = fil.Name
         rst.Update
      End If
   Next fil

   rst.Close
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in CopyCSVFilesToTable procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window