Importing excel tables into access

[kdbailey]

I am trying to write some code to import an excel table into access. I want to delete and append a table already in the database. I am having trouble writing the code to do this. If this is at all possible through excel, I would prefer to export the table from excel into access. Otherwise if that's not possible, a macro to import from excel will do.

[pbaldy]

I've only pulled from Access. If the structure of the spreadsheet will be the same, I'd use the same table. Here's an example:

CurrentDb.Execute "DELETE * FROM Excel401k"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Excel401k", strPath, True

where strPath contains the path to the Excel file to be imported and Excel401k is the name of the table in Access. The first line empties the table, the second imports the spreadsheet.

[pbaldy]

I should have added that if you want a new table, the second line will create it if it doesn't exist.

[kdbailey]

Thank you for responding to this post of mine, I have yet to check it. I've been busy with other tasks. I will give this a try as soon as possible.

[kdbailey]

Oh and how would I designate which sheet in excel to pull from?

[pbaldy]

Along the lines of adding the part in red:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Excel401k", strPath, True, "SheetName!"