Importing Multiple Selected Files To Access Database

[calvarado536]

I have a code set up on access that opens a select file dialog box and that with the selected file it imports it into a certain table. How do I get it to allow me to select multiple files and import all of the files selected. I know it's probably a simple fix but I can't seem to get it. I know I most likely have to change the allowmultiselect to true and i've tried that and it didn't work with the multiple files I selected. It only imported 1 file. Thank you in advance for the help!

Public Function file_Upload()
On Error GoTo ErrorHandler

   Dim fd As Office.FileDialog
   Dim varSelectedItem As Variant
   Dim strFileNameAndPath As String

   Set fd = Application.FileDialog(msoFileDialogFilePicker)
   
   With fd
      .AllowMultiSelect = False
      .Title = "Select Most Recent file"
      .ButtonName = "Select"
      .Filters.Clear
      .Filters.Add "File Type", "*.xls; *.xlsx; *.xlsm", 1
      .InitialView = msoFileDialogViewDetails
      .InitialFileName = "C:\reports\"
      If .Show = -1 Then
         strFileName = .SelectedItems(1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "table name", strFileName, True
      Else
         Debug.Print "User pressed Cancel"
         strFileNameAndPath = ""
      End If
   End With
   
   SelectXLFile = strFileNameAndPath
   
ErrorHandlerExit:
   Set fd = Nothing
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in SelectXLFile procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

 

[SkipVought]

Hi,

Where's your loop? You can't import them all at once. You need to loop thru each selected file, open, import, close.

 

[calvarado536]

Is that what is needed at the end? if so what kind of loop would it be? I'm very new with vba and I got this code from various other threads and I compiled it together. If you could help me out skipvought that would be much appreciated!

Would it be a loop until?

 

[SkipVought]

I read your post again and I see that you're using a select file dialog box. I don't believe that control has a multi select feature like a list box.

But in code you could open the folder, load a Listbox with the file names in the folder, and the use the multi select feature. That's assuming that all the files are in one folder.

 

[combo]

To allow multiselect (with CTRL key) change line:
[tt].AllowMultiSelect = False True[/tt]

Loop with:
[tt]For Each strFileName in .SelectedItems[/tt]
You need unique target table names in loop.

combo

 

[Andrzejek]

Try this:

Public Function file_Upload()
On Error GoTo ErrorHandler

Dim fd As Office.FileDialog
Dim strFileName[blue] As String
Dim i As Integer[/blue]
   
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .AllowMultiSelect =[blue] True[/blue]
    .Title = "Select Most Recent file"
    .ButtonName = "Select"
    .Filters.Clear
    .Filters.Add "File Type", "*.xls; *.xlsx; *.xlsm", 1
    .InitialView = msoFileDialogViewDetails
    .InitialFileName = "C:\reports\"
    If .Show = -1 Then[blue]
        For i = 1 To .SelectedItems.Count
            MsgBox .SelectedItems(i)
            'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "table name", strFileName, True
        Next i[/blue]
    End If
End With
      
ErrorHandlerExit:
   Set fd = Nothing
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in SelectXLFile procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[calvarado536]

Thank you very much andy! I used what you gave me and played around with and it finally worked for me. Here is the final code. I did make some adjustments to it but it finally works for me. Thank you so much!!!

Public Function file_Upload()
Dim fd As Office.FileDialog
Dim strFileName As String
Dim i As Integer
   
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .AllowMultiSelect = True
    .Title = "Select Most Recent files"
    .ButtonName = "Select"
    .Filters.Clear
    .Filters.Add "File Type", "*.xls; *.xlsx; *.xlsm", 1
    .InitialView = msoFileDialogViewDetails
    .InitialFileName = "C:\reports\" 'Insert File location in between parenthesis and don't forget to end with a     If .Show = -1 Then
       For i = 1 To .SelectedItems.Count
'Where it says "table name" change to the table you want to import to
     DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "table name", .selectedItems(i), True
        Next i
    End If
End With
End Function

I put comments in the code for anyone who wants to use it for their database.

thanks to everyone who helped with the process!