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
Microsoft Access

Avatar of undefined
Last Comment
Helen Feddema

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

<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
Helen Feddema

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

Helen Feddema

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23