List file names from a directory

maximyshka
maximyshka used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
dim fFile as string, strFolder as string, strFile as string

strFolder="c:\folder\"

fFile=dir(strFolder & "*.*")

while fFile<>""
        strFile=strFile & ";" & strFolder & fFile
       fFile=dir
wend

if len(strFile)>0 then
   strFile=Mid(strFile,2)
    me.comboName.rowsourcetype="value/list"
   me.comboname.rowsource=strFile
end if

if you want just the name of files, use this codes

strFolder="c:\folder\"

fFile=dir(strFolder & "*.*")

while fFile<>""
        strFile=strFile & ";" & fFile
       fFile=dir
wend

if len(strFile)>0 then
   strFile=Mid(strFile,2)
    me.comboName.rowsourcetype="value/list"
   me.comboname.rowsource=strFile
end if
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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
Top Expert 2009

Commented:
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

Top Expert 2009

Commented:
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

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