Back To Top

MS Access VBA Import all CSV Files in a Directory for versions 2000, 2002, 2003, 2007

Go here To Check if Table exists in VBA.
Go here To Import all MS Access Databases in a Directory in VBA.
Go here To Export Tables in VBA.

The VBA Import All Files Sample is available for download here in a zipped MS Access Database. The below picture (click to enlarge) shows the Single form.

MS Access VBA Import All Files within a Directory. 

The following code snippet will use VBA to Import All Comma Delimited Files within a Directory.

Public Sub
ImportFiles(spath As String )

'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   ' Can Return all file Names that exist in The CurrentProject.path & spath.
   ' Accepts
   ' spath as folder name
   ' Imports All files in a folder with extension CSV
   ''''>>> WARNING: Appends to Existing Tables of the same File Names!! <<<''''
   'USAGE: Place database in same directory as files to import or
   'USAGE: In directory containing the folder the files are located
   'Actual USAGE: ImportFiles "FolderName\" or ImportFiles "\"

On Error GoTo errhandler

Dim objFS As Object , objFolder As Object
objFiles As Object , objF1 As Object
strPath As String , strTableName As String
FileLen As Integer

'Ensure we have a "\" before we insert the file name
If Len(spath) > 0 Then
Left(spath, 1 ) <> "\" Then
spath = "\" & spath
If Right(spath, 1 ) <> "\" Then
spath = spath & "\"
End If
      End If

spath = "\"
End If

'Set import Path
strPath = CurrentProject.path & spath
'Ensure we have not made double "\" anywhere.
strPath = Replace(strPath, "\\" , "\" )

'Set Scripting Variables to retrieve files
Set objFS = CreateObject( "Scripting.FileSystemObject" )
Set objFolder = objFS.GetFolder(strPath)
Set objFiles = objFolder.files

'Loop through the files
For Each objF1 In objFiles

'Check for our file extension csv
If Right(objF1.Name, 3 ) = "csv" Then
FileLen = Len(objF1.Name)

'Set the Table Name to import as using the file name
         'Without the file extension
strTableName = Left(objF1.Name, FileLen - 4 )

'Uncomment the following Line to protect existing tables
         'If ifTableExists(strTableName) = False Then

         'Import the file as delimited
         'The Empty space between the Commas is where you would place
         'A saved Specifacation Name
         'However for my purposes one is not needed
DoCmd.TransferText acImportDelim, , strTableName, strPath & objF1.Name, True

'A couple additional Import types are available besides the one above
         'Additional Import Types:

         'Uncomment the following Line to protect existing tables
         ' Else
         'Uncomment the following Line to protect existing tables
         ' MsgBox "is here"
         'Uncomment the following Line to protect existing tables
         'End If
End If

'close out
Set objF1 = Nothing
objFiles = Nothing
objFolder = Nothing
objFS = Nothing

   Exit Sub

'Trap any errors
With Err
"Error " & .Number & vbCrLf & .Description, _
Or vbCritical, "ImportFiles"
End With

End Sub

For the above VBA Import all Files Function in a usable Form with Button, download the Sample database in ZIP format. The File includes two csv files to import. Place in the same folder as the database.

You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP - MS Access Tutorials.

For further MS Access Tutorials go here: MS Access tutorials

Go here To Check if Table exists in VBA.

Go here To Get all Fields in a Table with VBA


Did you find what you were looking for?
What would you suggest?
Your Name:
Your Message:
Your Email:



Custom Search