I'm getting a puzzling error on a function that I've been using for years to import multiple Excel worksheets into a table. This is working code that is actively in use to import other workbooks with multiple worksheets, however I'm working with a new workbook for this specific capture, and it's not cooperating.
As you can see from the below code, I'm grabbing a workbook and trying to import the 4 sheets by name (as I understand is the only way.) I assure you that the names of the sheets are correct, yet the error (3011 - The MS Access database engine could not find the object) states that it cannot find it. When I take the names off, it grabs Sheet1 by default and nothing else. What could be going on that's preventing it from recognizing the named sheets? What else can I try? Thanks!
Code:
Public Function import_lex_star_3m()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.AllowMultiSelect = True
.Title = "Please select STAR files"
.InitialFileName = Environ("USERPROFILE") & "\Desktop\DNFB Holds Report\Reports\" & "LEXINGTON*STAR*" & ".xlsx"
.Filters.Clear
.Filters.Add "Excel File", "*.xls*"
If .Show = True Then
For Each varFile In .SelectedItems
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "lex_star_3m_detail", varFile, True, "CCK"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "lex_star_3m_detail", varFile, True, "ST JOSEPH MAIN"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "lex_star_3m_detail", varFile, True, "ST JOSEPH EAST"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "lex_star_3m_detail", varFile, True, "JESSAMINE"
Next
End If
End With
End Function