I made a test excel file with three sheets (sht, sht1, sht2) put in some bogus data.
Made three tables in access with the names worksheet1, worksheet2, worksheet3, gave them field names identical to the first row on each of my worksheets and used this code and it worked perfectly:
Code:
Dim strSaveName As String
strSaveName = CurrentProject.Path & "\TestImport.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "worksheet1", strSaveName, True, "sht$"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "worksheet2", strSaveName, True, "sht1$"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "worksheet3", strSaveName, True, "sht2$"
the sheet name has to be encapsulated in "" marks and have a $ mark at the end of the name to tell access you're importing the whole sheet, not just a range of values on the first worksheet.