Access is confused as to the type of sheet style. Just because it has .xlsx, doesnt mean thats the format.
I think the best method would be to select the file in access,click an import button,
the button would run vb to open excel,
load the file, 'Save As' back into a known format,
then import that file.
Code:
'============
Sub ImportXLbook()
'============
Dim xl As Excel.Application
CONST kFILE2IMPORT = "c:\temp\Import.xls"
'you MUST put Excel in the program REFERENCES, in VBE menu (Ctl-G): TOOLS , REFERENCES
'checkmark the 'Microsoft Excel X.xx Object library'
Set xl = CreateObject("excel.application")
With xl
.Workbooks.Open "c:\MyImportSpreadsheet.xlsx"
.ActiveWorkbook.SaveAs kFILE2IMPORT, 56
.Quit
End With
DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel8 , "tbltmpCImport", kFILE2IMPORT , True
Set xl = Nothing
End Sub