DoCmd.TransferSpreadsheet

Import or export data to/from a spreadsheet file.

Syntax
      DoCmd.TransferSpreadsheet(TransferType, SpreadsheetType,
                     TableName, FileName, HasFieldNames, Range, UseOA)

Key
   TransferType    The type of transfer to make. AcDataTransferType 
                   default = acImport.

   SpreadsheetType The type of spreadsheet to import from, export to, or link to.
                   AcSpreadSheetType

   TableName      The name of the Access table.
                  For exporting, this can be the name of a SELECT query object.

   FileName       The file name and path of the spreadsheet.

   HasFieldNames  Use True (–1) to use the first row of the
                  spreadsheet as field names when importing or linking.
                  default= False (0) treat the first row as normal data.

                  When exporting, field names are always inserted into
                  the first row of the spreadsheet.

   Range          A valid range of cells or the name of a range
                  in the spreadsheet. This argument is valid only for importing.
                  default=entire spreadsheet.

   UseOA          This argument is not supported..

With a linked Excel spreadsheet, you can view and edit the spreadsheet data with Access while still allowing access to the data from Excel.

When Access data is exported to a spreadsheet, the field names are always inserted into the first row.

Examples

In VBA:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "T_Employees", "F:\Employees.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryEmployees", "F:\Employees.xlsx", True

in PowerShell:

$acExport = 1
$acSpreadsheetTypeExcel9 = 8
$db = New-Object -Comobject Access.Application
$db.OpenCurrentDatabase("F:\Demo\Test.mdb")
$db.DoCmd.TransferSpreadsheet($acExport, $acSpreadsheetTypeExcel9, "T_Sales", "C:\work\Sales.xls", $True)
$db.Quit()

“Traditionally, most of Australia’s imports come from overseas” ~ Keppel Enderbery

Related:

Importing Excel workbooks - MVPS Examples
TransferDatabase - Import or export data to/from another database.