How To Do A VBA CSV Import For One To Multiple Files

So you want to be able to import your csv for 1 to multiple files. Here’s how you do it.

1. up a form with a button.

2. After you click the button, you will get a File Dialog Box that will show you the csv files to choose from:

Here is the code behind the “Import” button:

Private Sub btnCSVImport_Click()
    'vba csv import

    Dim strSQL As String
    Dim strFileName As String
    Dim varCSVFiles As Variant
    
    On Error GoTo errhandler

    varcsvfiles1 = get_files(strStart, "Select Your CSV File")
    
    
    If Len(varcsvfiles1) Then
        varCSVFiles = Split(varcsvfiles1, ",")
        For i = LBound(varCSVFiles) To UBound(varCSVFiles) - 1
        
            'import all files selected
            DoCmd.TransferText acImportDelim, , "csv_import", varCSVFiles(i), False
                
        Next i
        
        'delete all of the "ImportErrors" tables
        CleanUpStrayTables
        MsgBox "Import Completed.", vbInformation
    Else
        MsgBox "You didn't select any files"
        Exit Sub
        
    End If
    
Exit Sub
errhandler:

    MsgBox Err.Number & " - " & Err.Description, vbInformation
    DoCmd.SetWarnings True

End Sub

Public Function get_files(start_here, title_bar As String) As Variant
    Dim intChoice As Integer
    Dim strPath As String
    Dim varSelectedItem As Variant
    
    'IMPORTANT: needs reference to the MS office object library
    
    'only allow the user to select one file
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .InitialFileName = start_here
        .Title = title_bar
    End With
    
    'make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    
    'determine what choice the user made
    If intChoice <> 0 Then
        'get the file path selected by the user
        For Each varSelectedItem In Application.FileDialog(msoFileDialogOpen).SelectedItems
            'Return File Name and Path
            'every file selected will be stored in the function's "array", delimited by the comma.
            get_files = varSelectedItem & "," & get_files '<< Something needs to happen here to put selected files (1 or more) into an array to return
        Next varSelectedItem
        
    End If
End Function

Sub CleanUpStrayTables()

    Dim intTable As Integer
    Dim tdf As DAO.TableDef
    Dim strTableName As String

    For intTable = CurrentDb.TableDefs.Count - 1 To 0 Step -1

        strTableName = CurrentDb.TableDefs(intTable).Name
        Debug.Print strTableName

        If InStr(1, strTableName, "ImportErrors") Then
            DoCmd.DeleteObject acTable, strTableName
        End If

    Next
    
End Sub
  

Since you are going to get the annoying "ImportErrors" tables, because we didn't use a specification, you probably want to delete them. That's why the "CleanUpStrayTables" procedure is here. It basically loops your tables and delete the ones called "ImportErrors".

Here is what it will look like if you don't use the "CleanUpStrayTables" procedure:

It's pretty straight forward:

Sub CleanUpStrayTables()

    Dim intTable As Integer
    Dim tdf As DAO.TableDef
    Dim strTableName As String

    For intTable = CurrentDb.TableDefs.Count - 1 To 0 Step -1

        strTableName = CurrentDb.TableDefs(intTable).Name
        Debug.Print strTableName

        If InStr(1, strTableName, "ImportErrors") Then
            DoCmd.DeleteObject acTable, strTableName
        End If

    Next
    
End Sub
  

That's all, let me you if you have questions, and share this with someone else.

By the way, if you got or are getting value from the VBA information, please click the "Donate" button to give me a small token of your appreciation, thanks!