MS Access – VBA – Import Directory Listing Into A Table

I was asked in a forum how one could automate importing the links (path & Filename) of all the files contained within a specified directory. It is relatively easy to accomplish and the procedure below is one possible method.

The procedure has 2 input variables:

strPath which is the full path of the directory whose files you wish to import all the file paths from
strFilter which is an optional input variable should you wish to refine what type of document is imported (for instance is you only want to import PDFs then you’d enter “pdf”, Word documents “doc”, and so on)

Do not forget to select the sSQL statement that suits your needs (simply comment out the other 2) and replace the YourTableName, YourTableFieldName, YourTablePathFieldName and/or YourTableFileFieldName to match your table and field(s) names.

'---------------------------------------------------------------------------------------
' Procedure : ImportDirListing
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Import the list of directory files into a table
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sPath     : Full path include trailing  ie:"c:windows"
' sFilter   : Extension of files ie:"pdf".  if you want to return a complete listing of
'               all the files enter a value of "*" as the sFilter
'
' Usage:
' ~~~~~~
' Import all the files
'   Call ImportDirListing("C:\Temp")
' Import all the jpg files
'   Call ImportDirListing("C:\Temp", "jpg")
' Import all the PDF files
'   Call ImportDirListing("C:\Temp", "pdf")
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2010-09-19              Initial Release (Forum Question)
' 2         2022-10-05              Update Header
'                                   Code Cleanup
'                                   Added a few sSQL variations
'---------------------------------------------------------------------------------------
Public Function ImportDirListing(sPath As String, Optional sFilter As String)
On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim sFile                 As String
    Dim sSQL                  As String

    Set db = CurrentDb()

    'Add the trailing  if it was omitted
    If right(sPath, 1) <> "\" Then sPath = sPath & "\"
    'Modify the strFilter to include all files if omitted in the function
    'call
    If sFilter = "" Then sFilter = "*"

    'Loop through all the files in the directory by using Dir$ function
    sFile = Dir(sPath & "*." & sFilter)
    Do While sFile <> ""
        'Debug.Print sFile 'Current file name

        'SELECT 1 of the 3 sSQL statements below that suits your needs/setup
        '*******************************************************************
        '1 - Import just the filename
        sSQL = "INSERT INTO [YourTableName] ([YourTableFieldName]) VALUES('" & sFile & "')"
        '2 - Import the path and filename in a single field
        sSQL = "INSERT INTO [YourTableName] ([YourTableFieldName]) VALUES('" & sPath & sFile & "')"
        '3 - Import the path and filename in a separate fields
        sSQL = "INSERT INTO [YourTableName] ([YourTablePathFieldName], [YourTableFileFieldName])" & _
               " VALUES('" & sPath & "', '" & sFile & "')"

        db.Execute sSQL, dbFailOnError
        'dbs.RecordsAffected 'could be used to validate that the
                             'query actually worked
        sFile = Dir
    Loop
    
Error_Handler_Exit:
    On Error Resume Next
    If Not db Is Nothing Then Set db = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Source: ImportDirListing" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

As always, I hope this is useful to someone.

Other Resources on the Subject

Dir function (Visual Basic for Applications)

Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

23 responses on “MS Access – VBA – Import Directory Listing Into A Table”

Phil Conte February 4, 2012 at 7:17 am

This is exactly what I want to do. I hope it works.

Lynn February 15, 2013 at 10:58 am

Thank you so much – works great!

admin Post authorFebruary 15, 2013 at 10:01 pm

Glad it helped!

Cathy Wen May 14, 2014 at 1:50 pm

I’m having trouble with this routine. This is what I put in to call the routine
whatever = ImportDirListing(“C:\Users\MCXW3\Documents\wip”, 0)

Unfortunately, on the very first call to Dir$ it returns nothing and exits the do while loop.

I’ve tried with asterisk instead of the zero or nothing with the same result. I am executing my access program from the “wip” directory that i am trying to list but i have tried moving the access project to another directory with the same result.

thanks for any help you can give me.

Daniel Pineault Post authorMay 14, 2014 at 2:06 pm

Firstly, the second input variable is optional, so if you do not plan of filtering the files in the directory by extension, then simply omit it altogether. Something like:

ImportDirListing(“C:\Users\MCXW3\Documents\wip”)

On the other had, if you do wish to filter the returned files based on a file extension, you can see by the second input variable’s declaration that it is expecting a string. Thus, you need to input a valid file extension surrounded by quotes. Something like:

ImportDirListing(“C:\Users\MCXW3\Documents\wip”, “xlsx”)

Mike July 6, 2016 at 2:49 pm

I know this is an old post so hopefully I can get help. Thank you in Advance. How would i make this pull up a dir of folders not files?

Daniel Pineault Post authorJuly 6, 2016 at 9:23 pm

Mike,

See if http://www.devhut.net/2016/07/06/ms-access-vba-import-listing-of-sub-directories-into-a-table/ does help you out.

John October 5, 2017 at 3:18 pm

I am sorry could you please give an example of how you can check in the immediate window in access 2016, thank you.

Daniel Pineault Post authorOctober 5, 2017 at 8:05 pm

How about altering the procedure every so slightly

Function DirListing(strPath As String, Optional strFilter As String)
' Author: CARDA Consultants Inc, 2007-01-19
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'              back to this site are allowed).
'
' strPath = full path include trailing  ie:"c:\windows\"
' strFilter = extension of files ie:"pdf".  if you want to return
'             a complete listing of all the files enter a value of
'             "*" as the strFilter
On Error GoTo Error_Handler
 
Dim MyFile  As String
 
'Add the trailing  if it was omitted
If Right(strPath, 1) <> "" Then strPath = strPath & ""
'Modify the strFilter to include all files if omitted in the function
'call
If strFilter = "" Then strFilter = "*"
 
'Loop through all the files in the directory by using Dir$ function
MyFile = Dir$(strPath & "*." & strFilter)
Do While MyFile <> ""
    Debug.Print MyFile
    MyFile = Dir$
Loop
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: ImportDirListing" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

Then in the immediate window you could call it like so

Call DirListing("C:\Users\TestUser\Documents\", "xlsx")

George K November 5, 2017 at 12:39 pm

Great stuff thanks for posting!

Albert March 7, 2018 at 3:54 pm

This worked for me like a charm! Is there anyway I can also grab the “Modified Date” as well (in a separate field of course)

Thanks in Advance!

Daniel Pineault Post authorApril 3, 2018 at 8:00 pm
Look at using FSO to pull such file information. Something along the lines of:
```
CreateObject("Scripting.FileSystemObject").GetFile("C:\...\...\YourFileName.Ext").DateLastModified
```

Brian P. June 11, 2018 at 12:19 pm

Looks good, and works for the first 20 files name or so, but then exits. There are probably 2500 files in this directory. Ideas?

Daniel Pineault Post authorJune 11, 2018 at 12:42 pm

I’d add a breakpoint in the code and check each loop for the values to see what it isn’t liking.

Could you have paths/filenames that are extremely long, some weird characters perhaps?

Perhaps you could make the code display the error and then Resume with the next file.

Greg August 8, 2019 at 9:33 am

How can I get this to loop through subfolder as well?

Daniel Pineault Post authorAugust 8, 2019 at 9:59 am

You can’t use this approach with sub-folders. Sadly Dir() will iterate incorrectly if called again within a Dir() loop (I found this out the hard way). You’d need to switch over to FSO or another approach.
1. Carl October 6, 2021 at 9:57 am
  
  Good advice never dies, right?! Do you have an example of an FSO code that could be used for this?
  
  Also, perhaps a bit off topic, I’ve been learning VBA a lot as it applied to Form objects, but how do I utilize it to fill a table? I see where I would add TableName and FieldName values, but where do I put this code?

Dian January 31, 2020 at 3:00 am

Hi Daniel,
Thanks for posting the codes . I am really a beginner of SQL and VBA, it seems that your codes should work perfectly but because of my poor knowledge I do not see the result as I would like to see .
I use the codes below, it seems that I did something wrong in defining strFileName.

Currently the result is a list of rows with with this value : “&strFileName&” , the total rows are total files which are in the folder.

   Dim strFileName As String
   Dim db     As Database
   Dim sSQL   As String
   Dim strPath As String
   Dim strFilter As String
      
   Set db = CurrentDb()

   
   strPath = "C:\Dian\"
   strFilter = "pdf"
   strFileName = Dir(strPath, vbDirectory)
   
   Do While strFileName  vbNullString
       If strFileName  "." And strFileName  ".." Then
             If (GetAttr(strPath & strFileName) And vbDirectory) = vbDirectory Then
            Debug.Print strFileName   
        End If    
    End If
    strFileName = Dir   
    
   sSQL = "INSERT INTO [Files](Fname)VALUES(""&strFileName&"")"
   db.Execute sSQL, dbFailOnError
   'strFileName = Dir()
   Loop

Would you kindly please help?
Thanks.

Didier Van Acker February 7, 2021 at 11:12 am

VALUES(‘” & strFilename & “‘);”
do not stick & and filename to each other, but seperate.
quotes might work like you have them too, but i’ve also seen ‘” or “”” work, so try it out.

Daniel Pineault Post authorFebruary 7, 2021 at 4:16 pm

Thank you for answering the question as I somehow complete missed ever seeing it come in.

Jay October 5, 2022 at 6:22 am

Thanks Daniel!

Couldn’t get it to work until I changed this line:
If Right(strPath, 1) “” Then strPath = strPath & “”
into:
If Right(strPath, 1) “\” Then strPath = strPath & “\”

(maybe that backward-slash (‘\’) gets removed in the code-field in your HTML-page ?)

Best,

Jay

Daniel Pineault Post authorOctober 5, 2022 at 7:23 am

Thank you for flagging the issue. I’ve revised the post and updated the code.

Andrew Watson February 16, 2025 at 11:27 am

This works great but after loading the table I have to delete a directory and its files from the table using a query.
Is it possible somewhere in the code to exclude a directory from being loaded?
Many thanks

Andy