I have come out of vacation mode and been testing further selecting multiple excel files with dialog box. It works great if I select one file at a time. I want to beable to select several files. I did change this line of code that allows me to select several files:
.AllowMultiSelect = True
When I open the table it shows only one file, which is the first file in the list.
I also get an error message when I choose CANCEL in the dialog box.
As for multiple files and rewriting code will the outcome be any different than what it is now?
If properly rewritten it could handle multiple files and import them.
Here is a version that works (I think).
Private Sub cmdImportexcel_Click()
Dim i As Integer
Dim zXLFPath As String
Dim zXLFName As String
Dim iFileType As Integer
'Requires reference to Microsoft Office 14.0 Object Library Office 2010.
'Note: Returns a fully qualified filename, e.g. d:\path\filename.ext
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Dim zCurDir As String
'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
'Allow user to make multiple selections in dialog box
.AllowMultiSelect = True
.Title = "Please select the file to import"
'Clear out the current filters, and add your own.
.Filters.Add "Excel 2003", "*.xls"
.Filters.Add "Access 2007-10", "*.xlsx"
'Set the initial directory using passed argument string
.InitialFileName = "D:\"
'Show the dialog box. If the .Show method returns True, the
'user picked a file. If the .Show method returns
'False, the user clicked Cancel.
If .Show = True Then
For i = 1 To .SelectedItems.Count
zXLFPath = .SelectedItems(i)
If UCase(Right(zXLFPath, 1)) = "X" Then
iFileType = acSpreadsheetTypeExcel12Xml
iFileType = acSpreadsheetTypeExcel12
DoCmd.TransferSpreadsheet acImport, iFileType, "BridesDB", zXLFPath, True
This code brings the FileDialog (that was previously a separate function ) back into the sub. Not that there was anything wrng with using a separate function, but I found the multi file easier to handle this way.
Is there a way for the user before "Student-" to input something like ANGB01? or is there a way for a query that is executing before the transferspreadsheet that has a parameter asking for the same variable, meaning ANGB01 to automatically be part of the filename? I hope I am being clear!
That works great. I did take out the backslash \ in front of Student and I got the results I wanted. The input box pops-up and the user can enter ANGB01 and it becomes part of the filename. The result is ANGB01Student and so on. It was so easy. Thank you very much.
is there a way for a query that is executing before the transferspreadsheet that has a parameter asking for the same variable, meaning ANGB01 to automatically be part of the filename? I hope I am being clear!
If the query parameter came from a form rather than a query prompt then you could include it in the file name automatically, rather than having to use an Input box to ask for it again.
I would suggest trying to create a Macro to import the Excel spreadsheet. The macro editor will help you with the parameters for importing. Then it is much easier to figure out DoCmd.TransferSpreadsheet.
Another thought that I had is, you don't need to import the data. I typically link to a file, then use a couple of queries to append the data to existing tables. Or you can create a Make Table query to create the data.
If you are going to do it regularly (weekly) then I would suggest linking so you don't have to add the data twice to your database.