This can be done using VBA with the TransferSpreadsheet command.
DoCmd.TransferSpreadsheet [Transfer Type], [Spreadsheet Type], [Table Name], [File Name], [Has Field Names], [Range]
Transfer Type - Refers to how you want to move the data into the database. For what you described, you will want to use acImport.
Spreadsheet Type - Refers to the type of spreadsheet you are trying to import. If you are using Access & Excell 2007, then you would use acSpreadsheetTypeExcel12. The context menu will give you all of the available options when you are typing out the code.
Table Name - The name of the table you want to import the data to. If this table exists, then Access will append the data to the table, otherwise it will create it. Note: if you are appending to an existing table, you will want to make sure that the column headings in your spreadsheet are identical to their respective columns in the table.
File Name - The name and full path of the file you wish to import.
Has Field Names - Specifies whether the first row of the spreadsheet contains the names of the fields. If you select True, Microsoft Access uses the names in this row as field names in the Microsoft Access table when you import or link the spreadsheet data. If you select False, Microsoft Access treats the first row as a normal row of data and will name the columns in the table Field1, Field2,...
Range - The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25. If there is more than one tab, then you can specify the tab as well; for example, Budget!A1:C7.
Your finished code would look something like this:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "MyTable1", "C:\Folder\MySpreadsheet.xlsx", True, Budget!A1:C7
You would then just enter another line for the table and data range you want to import. So your code would wind up looking like this:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "MyTable1", "C:\Folder\MySpreadsheet.xlsx", True, Budget!A1:C7
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "MyTable2", "C:\Folder\MySpreadsheet.xlsx", True, Expense!A1:E50
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "MyTable3", "C:\Folder\MySpreadsheet.xlsx", True, Income!A1:F24
