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.