Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Importing Multiple Selected Files To Access Database

Status
Not open for further replies.

calvarado536

Technical User
Aug 14, 2014
10
US
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! :)

Code:
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
 
Hi,

Where's your loop? You can't import them all at once. You need to loop thru each selected file, open, import, close.
 
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?
 
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.
 
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
 
Try this:

Code:
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.
 
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!!!

Code:
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!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top