Exporting and importing database tables in Microsoft Access using Excel file format without using format specification can be done by combining two DoCmd methods, these are:
OutputTo command is used to export tables/queries to an external file. Below is an example that exports a query to an Excel file.
DoCmd.OutputTo acOutputQuery, "Query01", acFormatXLS
The command will prompt automatically a file dialog box for you to specify where to save the exported data.
TransferSpreadsheet command is used to import external Excel file into a table in your Microsoft Access database. Below is an example that imports an external Excel file.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tmpTable", CurrentProject.Path & "\Query01.xls", True
These two commands are very useful in transferring data from one system to the other using any external device like USB or the Internet.