Import multiple files to multiple tables

[born2live]

Hi guys,
I made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.
At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:

Function GetAllFiles()
Dim fd As Object
Dim strFilter As String
Dim lngItems As Long

Const msoFileDialogOpen As Long = 3
Const msoFileDialogViewDetails As Long = 2

Set fd = FileDialog(msoFileDialogOpen)

With fd
.AllowMultiSelect = True
.InitialView = msoFileDialogViewDetails
.InitialFileName = "E:\Planning"
.Title = "Select your file(s)"
.Filters.Clear
.Filters.Add "All Files", "*.*"
.Show
End With

For lngItems = 1 To fd.SelectedItems.Count
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "1", fd.SelectedItems(lngItems), True
Next
End Function

Any helps will be appreciated.

 

[madefemere]

Hello,
And with :

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, lngItems, fd.SelectedItems(lngItems), True

 

[born2live]

Hello,
And with :

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, lngItems, fd.SelectedItems(lngItems), True

madefemere,
Thanks for the reply:
By changing the code to what you mentioned, the function (GetAllFiles()) starts to import the files and gives them a numeric name (from 1 to the number of files which I have selected) e.g. if I have selected two files, the function imports them with names "1" and "2".
But the thing I want to do is that, the function gets file's original name and stores them in access with their original name.
Thanks everyone in advance

 

[madefemere]

I suggest you to use "For each" syntaxt in place of the simple For ...
With the InstrRev and Mid functions, you can extract the names of the files and use there as names of the tables. So you have the beloww code :

For Each varFile In fd.selecteditems
FileName = Mid(varFile, InStrRev(varFile, "\") + 1, InStrRev(varFile, ".") - InStrRev(varFile, "\") - 1)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, FileName, varFile, True
Next

Where you define varFile as Variant and FileName as string.

Have a good continuation

 

[born2live]

I suggest you to use "For each" syntaxt in place of the simple For ...
With the InstrRev and Mid functions, you can extract the names of the files and use there as names of the tables. So you have the beloww code :

For Each varFile In fd.selecteditems
FileName = Mid(varFile, InStrRev(varFile, "\") + 1, InStrRev(varFile, ".") - InStrRev(varFile, "\") - 1)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, FileName, varFile, True
Next

Where you define varFile as Variant and FileName as string.

Have a good continuation

Dear madefemere,
It worked perfectly. Thank you so much for the time you spent on my problem. Have a nice time buddy