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
   Dim
objFiles As Object , objF1 As Object
   Dim
strPath As String , strTableName As String
   Dim
FileLen As Integer

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

   Else
     
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:
         'acImportFixed
         'acImportHTML

         '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
   Next

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

   Exit Sub

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

   Resume
ExitHere
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?
mail@eraserve.com
Your Name:
Your Message:
Your Email:

    

 

Custom Search