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