I have an Excel workbook that can have multiple sheets. The data on the sheets are formatted exactly the same. Right now the user is copying the extra sheets to sheet one and importing it into the DB. I want to allow them to just import it all at once without having to do that. I found, here on EE, an example that uses a combobox to allow them to select what they want imported. I couldn't get it to work to just import everything. Here is the code I am currently using - this actually allows them to grab multilple Excel files as well. I just want one file, but all sheets in that file. Thanks for any help you can give me. I truly appreciate it.
Dim fdg As FileDialog, vrtSelectedItem As Variant
Dim strSelectedFile As String
Set fdg = Application.FileDialog(msoFileDialogFilePicker)
fdg.InitialFileName = DLookup("DefaultDir", "defLocation")
.Filters.Add "Excel Files", "*.xls"
.Filters.Add "Excel 2007", "*.xlsx"
.AllowMultiSelect = True
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
strSelectedFile = vrtSelectedItem
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Scrap", strSelectedFile, True, , False
' If Not IsEmpty(strSelectedFile) Then
If Len(Trim(strSelectedFile) & "") > 0 Then
DoCmd.OpenForm "UpdateTables", , , , , , False
DoCmd.Close acForm, "ScrapAnalysis", acSaveNo
DoCmd.OpenQuery "Scrap Query"
DoCmd.OpenForm "FileNotSelected", acNormal, , , , , False
' DoCmd.Requery "Reasons"
' DoCmd.Requery "ToolNumbers"