Import multiple files to multiple tables (1 Viewer)

born2live · Sep 21, 2014

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 · Sep 22, 2014

Hello,
And with :

Code:

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

born2live · Sep 22, 2014

madefemere said:
Hello,
And with :

Code:

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 · Sep 22, 2014

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 :

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 · Sep 23, 2014

madefemere said:
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 :

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

Import multiple files to multiple tables (1 Viewer)

born2live

Registered User.

madefemere

Registered User.

born2live

Registered User.

madefemere

Registered User.

born2live

Registered User.

Similar threads

Users who are viewing this thread